Extract all the numeric values from the right of any alphanumeric string

kuldeepjainesl · Jun 19, 2012

Hi,

Might be very easy but i could not figure it out in my past. Now putting this front od experts to get this sort out.

Problem: Alphanumeric string may be any combination of numeric values and alphabets but rightmost character will be numeric. Sample database given below

[pre]

Code:

&#34;A&#34;              &#34;B&#34;
AXP003489        3489
A7GP00989        989
900089998        900089998
AHJGKK009        9

[/pre]
Need a formula to extract the rightmost numeric value from "A" coloum to "B" as illustrated above. I will use it as a number so will multiply this to 1 to get it converted in to number so it can be used further.

I am stuck on how to find the position of rightmost alphabet? else I would have used Len and Right function to extract it.

Regards,

Kuldeep

Hui · Jun 19, 2012

Kuldeep

Looking at the first line

AXP003489 3489

Should the answer be:

003489 3489 =RIGHT(A1,LEN(A1)-MIN(IFERROR(FIND(lstNumbers,A1),""))+1) Ctrl+Shift+Enter

3489 =RIGHT(A1,LEN(A1)-FIND(" ",A1))

9 =Right(A1,1)

Note in the first formula there is a named range which is defined as

lstNumbers ={0;1;2;3;4;5;6;7;8;9}

You could also review todays post: http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

Satish.digit · Jun 19, 2012

Hi Kuldeep,

You can use a VBA also for this below is the code

Function GetNums(target As Range)

Dim MyStr As String, i As Integer

MyStr = ""

If Len(target.Value) = 0 Then GoTo GoExit

For i = 1 To Len(target.Value)

If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)

Next i

GoExit:

GetNums = MyStr

End Function

Copy this code in to Module screen and go to the excel sheet:

A Column : AXP003489 then type in B Column : =getnums(a1)

Then you will get only numbers in A column like 3489

Satish.

kaushik03 · Jun 19, 2012

you can also look at the following link for further understanding

http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx

Kaushik

kuldeepjainesl · Jun 19, 2012

Thanks for all for quick response but all three does not meet the requirment in full.

Hui >>>>>> 3489 =RIGHT(A1,LEN(A1)-FIND(" ",A1)). This is what i desired as answer but there is no space what you have used in formula. This is due to formatting issue in above post. Data is "AXP003489" and answer required is "3489"

Satish Digit>>>>>> Thanks for the UDF but it fails for the condition given at second sample data line. String is "A7GP00989" and answer required is "989". For this type of data this UDF does not work as it is returning as 700989. please note all sample data have diffrent combination.

Kaushik03>>>>>>>>> your suggested formula from link is an array formula " 1*MID(A4,MATCH(TRUE,ISNUMBER(1*MID(A4,ROW($1:$9),1)),0),COUNT(1*MID(A4,ROW($1:$9),1)))"

This also fail in the data set given at second sample data line. String is "A7GP00989" and answer required is "989". For this type of data this does not work & giving error.

I hope to get further possible solutions to this in one.

Regards,

Kuldeep

Luke M · Jun 19, 2012

I think Hui's formula was close, but it pulls from the first number. To get ONLY the last set of numbers, this array (Ctrl+Shift+Enter) formula will work:

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

kuldeepjainesl · Jun 19, 2012

You are great Luke M. Hats off to you. I was searching it from a while and you made it.

Cheers,

Kuldeep

kuldeepjainesl · Jun 19, 2012

Let me admit that it was not so easy as i was thinking....

Luke M · Jun 19, 2012

You're very welcome Kuldeep. Glad I could be of assistance.

kuldeepjainesl · Jun 19, 2012

I would request if this can be explained in formula forensic at chandoo.org.

Regards,

Kuldeep

Luke M · Jun 19, 2012

Starting from the inside and working out:

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

Bolded section is building a range reference. What we really want is to have an array of numbers, 1 to n with n being the number of characters in our word. So, the LEN function determines the value of n, and we use the INDIRECT function to turn this into the reference 1:n. For use of example, let's say the word was "12ab34". In this case, n = 6.

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

Next, the row function converts our range of 1:6 into the numbers 1,2,3,4,5,6. Remember, we confirmed this as an array formula, hence we get the array of numbers. This array is then fed into the MID function. MID lets us choose our text, and then look at a specific starting letter and amount of characters. Our array of numbers will let us look at each individual character. Note that the 3rd argument in the MID function is a 1, aka, we're looking at just 1 character at a time. So, the array now looks like

"1","2","a","b","3","4"

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

We multiply the array by 1 to force the numbers currently stored as text to become true numbers. It will also have the effect of creating an error where the non-numbers were. Array now looks like:

1,2,#VALUE!,#VALUE!,3,4

The ISERROR function converts this set of numbers and errors into a True/False array that looks like this:

False,False,True,True,False,False

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

The other array we're building is just the numbers. I've already explained how this works when we used it in the MID function. Suffice to say, it creates this array

1,2,3,4,5,6

We then multiply the 2 arrays against each other. True is counted as 1, False is counted as 0. This creates the array:

0,0,3,4,0,0

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999)

The MAX function returns the largest value from our array, 4. This tells us that the last non-number character is in the 4th spot. However, we want to start at the character 1 after that, so we add 1 (getting 5). The MID function then, says to take the word from A1, start at the 5th character, and return the next 999 characters (an arbitrary large number that will guarantee we return everything beyond the 5th character. At this point, we have the "text"

"34"

However, since we want to use this as a number later, we multiply the whole thing by 1, forcing it to become the number:

34

Haseeb A · Jun 19, 2012

Hi Kuldeep,

Another one:

=LOOKUP(9E300,RIGHT(A1,ROW(INDIRECT("1:15")))+0)

NARAYANK991 · Jun 20, 2012

Hi Haseeb ,

Thanks for this amazing formula.

Narayan

kuldeepjainesl · Jun 20, 2012

Hi Haseeb,

Amazing response, Thanks, This formula is working...but sorry I could not understand, how?

. I could not catch the Lookup value "9E+300" & What this represents?

One more observation is that lookup value in your formula is getting changed from "9E300" to "9E+300" Why?

"LOOKUP(9E300,RIGHT(A1,ROW(INDIRECT("1:15")))+0)"

getting converted in to

"LOOKUP(9E+300,RIGHT(A1,ROW(INDIRECT("1:15")))+0)"

Regards,

Kuldeep

Luke M · Jun 20, 2012

@Haseeb

Brilliant formula!

@Kuldeep

If Haseeb will indulge me to explain...

9E300 is the notation for saying 9 x 10 ^ 300, ie scientific notation. XL automatically adds the plus symbol to distinguish between 9x10^300 and 9^10^(-300). Just like any other normal positive integer, there is no real difference between showing the positive sign.

How the lookup works:

By feeding the array of row numbers into the RIGHT function, the formula builds an array of text strings, increasing in length. Example:

Starting with "1234abcd987"

The RIGHT array function builds:

"7","87","987","d987","cd987","bcd987","abcd987","4abcd987","34abcd987","234abcd987","1234abcd987"

The +0 after that makes sure that all the number entries get converted from text strings to true numbers.

The LOOKUP then is looking for number closest in value to 9E300 (an arbitrary large number). As everything after the 3rd value contains text, the largest number found is 987, and the formula correctly gives an answer of:

987

The only change I would suggest is adding a LEN function in case you have text strings longer than 15 letters:

=LOOKUP(9E300,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0)

kuldeepjainesl · Jun 20, 2012

You are a true legend....Luke M.

You explained it in a way that anyone can understand. Just confirming that Len function is just to make it useful for any length of string. if a user know what may be the maximum number of input string, he can always adjust the 1:15 to known maximum number.

Regards,

Kuldeep

Luke M · Jun 20, 2012

Kuldeep,

Thanks for the kind words sir. I do try to make sure that I pass on the knowledge I've gained. =)

You're understanding of the LEN function is correct, it's just to make it more useful for any length. You could alternatively change the 1:15 to whatever length you think would be the maximum.

Sajan · Jun 20, 2012

Hi Haseeb,

You take the prize with your clever LOOKUP() formula. Good one!

Cheers,

Sajan.

kuldeepjainesl · Jun 29, 2012

Hi Luke / Narayan / SirJB7 / Hui,

Everything went fine....and suddenly i got a shock when i came across a string having suffix at their end..

Example....

AXP003489P

A7GP00989P

900089998P

AHJGKK009P

I got the two gems in above discussion....

=LOOKUP(9E300,RIGHT(A1,ROW(INDIRECT("1:15")))+0)

OR

=1*MID(A1,1+MAX(ROW(INDIRECT("1:"&LEN(A1)))*ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),999) + Ctrl+Shift+Enter

For given string these two gems fails as it do not get number at right side.

My requirement is still same that i want the last numeric set of numbers.

Please help....

Regards,

Kuldeep

kuldeepjainesl · Jun 29, 2012

Hi,

What i could think of to check the last character of string by

=IF(ISNUMBER(RIGHT(A1,1)*1),A1,LEFT(A1,LEN(A1)-1))

and get the tuned data in helper coloum which can feed to both of the gems i received.

Please comment on the same or if this can be embed in any of the above gems, please suggest.....

Regards,

Kuldeep

Luke M · Jun 29, 2012

try this UDF then

[pre]

Code:

Function LastNumber(s As String) As Long
Dim xNumber As String
Dim NoNumYet As Boolean
Dim TempValue As String

&#39;No value input
If s = &#34;&#34; Then Exit Function
&#39;No numbers in string
If Not s Like &#34;*[0-9]*&#34; Then Exit Function

NoNumYet = True

For i = Len(s) To 1 Step -1
xNumber = Mid(s, i, 1)
If xNumber Like &#34;[0-9]&#34; Then
NoNumYet = False
TempValue = xNumber & TempValue
Else
&#39;Escape one we have the last number
If Not NoNumYet Then Exit For
End If
Next

LastNumber = TempValue * 1

End Function

[/pre]

kuldeepjainesl · Jun 29, 2012

Hurrah....You have done it... it worked..

Can you suggest that in case only one character does my formula seems to be good choice.

Regards,

Kuldeep

Luke M · Jun 29, 2012

Yes, your formula will work just fine if it's only a 1 character problem. I wasn't sure how extensive the issue was.

Haseeb A · Jun 29, 2012

Hi Kuldeep,

If you only have a single non numeric character at end, you can use

=LOOKUP(9E+300,RIGHT(LEFT(A1,LEN(A1)-1),ROW(INDIRECT("1:15")))+0)

Or, this version will work from last to next non numeric.

=LOOKUP(9E+300,RIGHT(LEFT(A1,MATCH(10,INDEX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,0))),ROW(INDIRECT("1:15")))+0)

Eg:

AXP003489P = 3489

A7GP00989PPPASD = 989

900089998P00145PP = 145

AHJGKK009P12 = 12

ABC9EFGKLMOPQ = 9

ABCHUP = 0 (No numbers)

Hope this helps

Haseeb A · Jun 30, 2012

Sorry, forgot to add 0 in front of text to return zero if numbers are not in text.

=LOOKUP(9E+300,RIGHT(LEFT(0&A1,MATCH(10,INDEX(MID(0&A1,ROW(INDIRECT("1:"&LEN(A1)+1)),1)+0,0))),ROW(INDIRECT("1:15")))+0)

Extract all the numeric values from the right of any alphanumeric string

Member

Excel Ninja

Member

Member

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Active Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Active Member

Active Member