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:

- Starting position of the first number in a text string (use
**FIND**function) - Split the text numbers (use
**MID**function) - 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.

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

The result was this

28,072

It's missing the first digits 1,0

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

Thanks a lot for this. Excel is just sweet