Table of Contents
You can use the following formula to extract the first number from a string in Excel:
=LEFT(TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")),1)
This particular formula will extract only the first number from the string in cell A2.
For example, if cell A2 contains the string 622 dollars then this formula will simply return 6.
The following example shows how to use this formula in practice.
Example: How to Extract First Number from String in Excel
Suppose we have the following list of strings in Excel:
Suppose we would like to extract only the first number from each string.
We can type the following formula into cell B2 to do so:
=LEFT(TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")),1)
We can then click and drag the formula down to each remaining cell in column B:
Column B now contains only the first number from each of the corresponding strings in column A.
How This Formula Works
Recall the formula that we used to extract the first number from the string in cell A2:
=LEFT(TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")),1)
First, ROW(INDIRECT(“1:”&LEN(A2))) returns a series of numbers from 1 to the length of the string.
Next, (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) converts all text characters to #VALUE! errors.
Next, IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) removes all $VALUE! errors.
Next, TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) joins together all the numbers that remain and ignores any blanks.
Lasty, LEFT(TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)),1) extracts only the first digit from the left.
The end result is that we’re able to extract only the first number from the string.