How To Find The First Number In A Text String Use Excel Formula

Sometimes you may want to find the first number in a text string, and deal with the numbers. In this Case, we only use excel formula to extract the first number.

Complete formula

This formula find the first number in a text string from the cell A1, if cell A1 has not numbers, then returns empty.

For Excel 2007 or later:

=IFERROR(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))),"")

For Excel 2003 or earlier:

=IF(ISERROR(--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))),"",LOOKUP(9.99999999999999E+307,--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))))

Note: The formula in the example must be entered as an array formula. After copying the formula to a blank cell, select the cell, press F2, and then press CTRL+SHIFT+ENTER.

This formula working perfect with European number format, 2 numbers, none numbers, large numbers, the results:

How this formula working

This is a nested formula. The ROW function, FIND function, MIN function, MID function, LEN function, INDIRECT function, and the LOOKUP function are nested within the IFERROR function.

To extract the first number from a text string, we need to know 3 things:

1. Starting position of the first number in a text string (use FIND function)
2. Split the text numbers (use MID function)
3. Find the MAX numbers (use LOOKUP function)

For example, in text US$4,567.08 the number starts at 4th letter and has a length of 8. So, if we find the starting position of the first number: 4, and split the text number with 4, 4, 4,5, 4,56, 4,567, 4,567., 4,567.0, 4,567.08, then find the MAX numbers: 4,567.08, it's the result that we want.

Step 1: find the position of numbers

we use the formula: FIND(ROW($1:$10)-{1},A1&56^7) to find the position of numbers.

ROW($1:$10) = {1;2;3;4;5;6;7;8;9;10}

ROW($1:$10)-{1} = {0;1;2;3;4;5;6;7;8;9}

56^7 = 1727094849536, sames as 0123456789, it's include the numbers 0 to 9. but it's simplify and shorten.

FIND(ROW($1:$10)-{1},A1&56^7)
= FIND({0;1;2;3;4;5;6;7;8;9},"US$4,567.081727094849536")
= {10;12;14;23;4;6;7;8;11;17}

Step 2: find the position of first numbers

MIN(FIND(ROW($1:$10)-{1},A1&56^7)) = 4, returns the starting position of the first number.

Step 3: split the first numbers

LEN(A1) = 11

ROW(INDIRECT("1:"&LEN(A1)))
= ROW(INDIRECT("1:11"))
= {1;2;3;4;5;6;7;8;9;10;11}

MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))

=

MID("US$4,567.08",4,{1;2;3;4;5;6;7;8;9;10;11}))

=

{"4";"4,";"4,5";"4,56";"4,567";"4,567.";"4,567.0";"4,567.08";"4,567.08";"4,567.08";"4,567.08"}

Step 4: text numbers to numbers

In step 3, we split the first numbers, but it's text numbers, not real numbers. So, we use -- formula convert text numbers to real numbers.

--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1))))

= {4;#VALUE!;#VALUE!;#VALUE!;4567;4567;4567;4567.08;4567.08;4567.08;4567.08}

Step 5: find the max numbers of split

we can use LOOKUP function to do this.

LOOKUP(9.99999999999999E+307,--MID(A1,MIN(FIND(ROW($1:$10)-{1},A1&56^7)),ROW(INDIRECT("1:"&LEN(A1)))))

= 4567.08

Note: read more about 9.99999999999999E+307

Step 6: trap and handle errors

If the text string doesn't have numbers, it returns #N/A, for example, the text Tom and Jerry have no numbers, the formula returns #N/A. So, we use IFERROR function to trap and handle this errors. If we find the first number, the IFERROR function returns the first number, if the text string doesn't have numbers, it returns empty.

Download the example

click here to download the example to easier understand this formula.