# 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`

### 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.

1. U4

Thanks a lot for this. Excel is just sweet

2. I have noticed you don’t monetize excelhowto.com, don’t waste your traffic,
you can earn additional bucks every month with new monetization method.
This is the best adsense alternative for any type of website (they approve all websites), for more details
simply search in gooogle: murgrabia’s tools

3. ExcelHowTo

I attempted to use your formula on a cell with this pasted in:
PPT 1,028,072 FXT 2,098,898 TOTAL 3,126,970