Excel: How to Extract First Number from String


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:

Excel extract first number from string

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)

Here is how this formula works:

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Extract Decimal Number from String
Excel: How to Extract Text Between Two Characters
Excel: How to Extract Text After a Character
Excel: How to Extract Text Before a Character

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *