Search for number in a text string

[CarlCarlos]

Dear all,

I have a list of character strings (examples below) which are likely to have a digit or two in them (1 to 12)

I need a formula that can identify the numbers within that string, is that possible?

i.e.

afafafaf1fafaf
awefrrd6ewtwtg
bb12rrwa

the strings are differing in length and the number could be 1 or two digits and do not necessarily reside in the same place for each string.

 

[pgc01]

I guess I was inspired!

... I made a slight mod tho

Function GetNumber(s As String, Optional lInd As Long = 1)

That way I can omit the second argument and it'll default to extracting the first number.

Thanks Weaver, for the improvement. It makes all the sense, that's how it should have been from the start.

...
Can you explain how the =-LOOKUP(0,-MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2})) works?

This formula could be handy for me in the future.

Biz

Hi Biz

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

Since in this case we have a number with either 1 or 2 digits, the Mid() formula extracts 2 strings, just the first digit, or the first digit and the following character.

Ex.: "ab12cd": you get {"1","12"}
Ex.: "ab1cde": you get {"1","1c"}

by applying a minus before the array, you convert its values to number, or to error, if it's not possible to convert.

Ex.: "ab12cd": you get =-LOOKUP(0,{-1,-12})
Ex.: "ab1cde": you get =-LOOKUP(0,{-1,#VALUE!})

As you know, when the lookup value is bigger than any of the values in the lookup array, the Lookup() function returns the last value in the array. We use zero as the lookup value, bigger than any negative number, and so we get the last value, -12 in the first case and -1 in the second.

Notice that it's equivalent to

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

In this case we also know that 100 is bigger than any number with 2 digits.

HTH

 

[Biz]

I guess I was inspired!




Thanks Weaver, for the improvement. It makes all the sense, that's how it should have been from the start.




Hi Biz

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

Since in this case we have a number with either 1 or 2 digits, the Mid() formula extracts 2 strings, just the first digit, or the first digit and the following character.

Ex.: "ab12cd": you get {"1","12"}
Ex.: "ab1cde": you get {"1","1c"}

by applying a minus before the array, you convert its values to number, or to error, if it's not possible to convert.

Ex.: "ab12cd": you get =-LOOKUP(0,{-1,-12})
Ex.: "ab1cde": you get =-LOOKUP(0,{-1,#VALUE!})

As you know, when the lookup value is bigger than any of the values in the lookup array, the Lookup() function returns the last value in the array. We use zero as the lookup value, bigger than any negative number, and so we get the last value, -12 in the first case and -1 in the second.

Notice that it's equivalent to

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

In this case we also know that 100 is bigger than any number with 2 digits.

HTH

Thank u mate again.

Biz