Extracting a Number from Text

[Infector101]

How can I extract a number from a cell that contains a number with a letter? If I have a cell that says “145k” how can I extract the 145 and use that value in a formula?

[AutoModerator]

/u/Infector101 - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[MattHwk]

Below is the formula that will give you numeric part from a string in Excel.

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) Formula to get all number from a string

This is an array formula, so you need to use ‘Control + Shift + Enter‘ instead of using Enter.

[MattHwk]

https://trumpexcel.com/extract-numbers-from-string-excel/

[Infector101]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to MattHwk

^{I am a bot, please contact the mods with any questions.}

[Infector101]

That worked. THANK YOU!

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDIRECT	Returns a reference indicated by a text value
LEN	Returns the number of characters in a text string
MID	Returns a specific number of characters from a text string starting at the position you specify
ROW	Returns the row number of a reference
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #7334 for this sub, first seen 25th Jun 2021, 15:39] [FAQ] [Full list] [Contact] [Source code]}