Return the first number in a text string

Is there an easy method for returning the first number in a text string?

If the string is --> it returns a Number.

33A --> 33
432ABC --> 432
5Bob --> 5

I thought there was an Excel function (might have been a VBA function) that would treat a text sting as a number and return the first number of the string. I can't seem to find it, hiding in my past memory.

Thanks in advance. This might be very easy..

Maybe this

=REPLACE(A1,MATCH(1,(CODE(MID(A1,ROW($A$1:$A$255),1))<90)*(CODE(MID(A1,ROW($A$1:$A$255),1))>=65),0),255,"")+0

	A	B
1	33A	33
2	432ABC	432
3	5Bob	5

Or this

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+0

Try this array formula if there is only one number string

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

See here
http://office.microsoft.com/en-ca/ex...001154901.aspx

or the age-old
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

Here is my problem. Excel is so big and has so many functions, I can't retrieve what I thought was true or easier.

After a lot of searching. I was remembering the VBA "Val()" function which takes the first part of a string, until it hits a letter, and returns the number equivalent.

I thought, how simple this is and I should remember it for when I need it sometime. Find below the User Defined Function that does the work of those formulas above.

Please Login or Register  to view this content.

To use the above UDF you need to copy the above 3 line into a Module. Then
Example: in Cell A1 you put "42 Is what I want!"
In B1 you would use the UDF with the formula of "=Str2Num(A1)"

I remember being intrigued on how easy the VBA Val() function could return the first number in a string while using normal Excel functions were much harder.

I'd bet there is a Control+Shift+Enter function that would replace all letters with "" and give another easier answer without using VBA...???

Hi Marvin,

Can you just clarify something?

You say that you're looking for "the first number in a text string", yet your three examples all have the number at the very start of the string. Is it in fact just these string types you're interested in, or did you simply forget to give other examples where that is not the case?

Cheers

Hi,

Yes I meant first number before any letters. Like

123abc456 --> 123

Ah, then in that case things are much simpler:

=LOOKUP(1E+307,0+LEFT(A1,ROW($1:1000)))

Edit: on second thoughts, this will fail for strings such as: 12-4, 1E3, etc.

Regards

In that case:

=LEFT(A1,MATCH(FALSE,INDEX(ISNUMBER(0+MID(A1,ROW($1:$1000),1)),,),0)-1)

Regards