How to find number at end of a cell

[davidman1]

Hi

I have a column which lists companies and their ID number in the form "Company name - ID 4892"

Is there a way to reference this cell so I just get the number at the end? I did use =RIGHT(A2,4) but that doesn't work when there is a 3 or 5 digit code.

[daddylonglegs]

For up to 5 digits try

=LOOKUP(10^5,RIGHT(A2,{1,2,3,4,5})+0)

[TMS]

If you always have "ID " in front of the number, try:

=RIGHT(A1,LEN(A1)-FIND("ID ",A1)-2)


Regards, TMS


Edit: to return a numeric result, use:

=--RIGHT(A1,LEN(A1)-FIND("ID ",A1)-2)

[Ghozi Alkatiri]

try it
=--SUBSTITUTE(A1,"ID"," ")

[davidman1]

For up to 5 digits try

=LOOKUP(10^5,RIGHT(A2,{1,2,3,4,5})+0)

If you always have "ID " in front of the number, try:

=RIGHT(A1,LEN(A1)-FIND("ID ",A1)-2)


Regards, TMS


Edit: to return a numeric result, use:

=--RIGHT(A1,LEN(A1)-FIND("ID ",A1)-2)

try it
=--SUBSTITUTE(A1,"ID"," ")

Thanks - the first two work, the last one doesn't since there's also the company name at the start of the cell (get rid of that and its fine though).

I don't understand how the first one works but it does. Thanks for letting me know about the -- thing too, I'd never seen that before.

[Aladin Akyurek]

Hi

I have a column which lists companies and their ID number in the form "Company name - ID 4892"

Is there a way to reference this cell so I just get the number at the end? I did use =RIGHT(A2,4) but that doesn't work when there is a 3 or 5 digit code.

1.

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0

2.

=LOOKUP(9.99999999999999E+307,--RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))

For more on 9.99... and its role in the LOOKUp expression:

http://tinyurl.com/alpxzho
http://tinyurl.com/bbt2ant

3.

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))+0

4. An old one...

=RIGHT(A2,SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))))+0