FIND the first (and any) number within a cell

[Matt]

is there some sort of wildcard that can be used to represent any number? I
would like to find the first number in a cell.

 

[Soundar]

Hi Matt,

Consider a cell contains both alphabet and numeric say cell A1 contains as
follows

Soundar1

Use the following formula in B1(or where ever your want), you will get
result as 1

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),1)

and press control+shift+enter. You will get first number from the cell A1.

Note it will work upto 9 characters length in A1, if your text length is
more, change $9 to the length you want.

For more details information please refer below link

http://office.microsoft.com/en-us/excel/HA011549011033.aspx

Hope this will clear your doubt.

Regards,
Soundar.


and give

 

[Matt]

Thank you Soundar1.

Hi Matt,

Consider a cell contains both alphabet and numeric say cell A1 contains as
follows

Soundar1

Use the following formula in B1(or where ever your want), you will get
result as 1

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),1)

and press control+shift+enter. You will get first number from the cell A1.

Note it will work upto 9 characters length in A1, if your text length is
more, change $9 to the length you want.

For more details information please refer below link

http://office.microsoft.com/en-us/excel/HA011549011033.aspx

Hope this will clear your doubt.

Regards,
Soundar.


and give