How can I extract the first number from a string using Excel?

By /

Extracting the first number from a string in Excel can be achieved by using the LEFT and FIND functions. The LEFT function allows you to extract a specific number of characters from the beginning of a string, while the FIND function helps to locate the position of a specific character or text within a string. By combining these two functions, you can easily extract the first number from a string in Excel. Simply use the FIND function to locate the position of the first number in the string, and then use the LEFT function to extract that number. This method can be useful for organizing data and performing calculations in Excel.

Excel: 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)

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:

x

Related terms:

  1. Excel: Extract Date from Text String How do i extract a date from a text string?
  2. Excel: Extract First Number from String
  3. Extract First Character from String in Excel
  4. How to Excel: Extract Decimal Number from String
  5. How to Extract Email Address from Text String in Excel?
  6. How can I split a string in a PySpark column and extract the last item from the split list?
  7. Extract Substring Starting from End of String
  8. How do I extract a string before a space in R?
  9. How do I extract a string after a specific character in R?
  10. How can I extract a date from a text string in Google Sheets?