IF statement formula trying to pull numbers that vary in # of digits

[djmatok]

I can't explain this properly. I am trying to pull from a text string numbers but these numbers vary in the # of digits what formula can i use. I tried this formula and it gives me a false result. Please help.

jane doe 123456 formula to pull "123456" = if(right(a1,6))

clause 123 formula to pull "123456" = if(right(a1,3)


how can I make a standard formula that will pull all possibilities ? thanks

[Jonmo1]

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

[djmatok]

Great ! this actually worked but I also have some items that are like this

SamJones85467 with no space .what can I add to this formula to pull just the last 5 numbers?

[Jonmo1]

Try this, assuming there will be no other numbers in the string prior to the zip.

=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))+0

[djmatok]

CanaanSmith60991 result with 2nd formula 6099

[Jonmo1]

works for me on all examples you posted
EFdjmatok.xlsx

[djmatok]

Hey Jonmo,
I would like to ammend the original formula that you gave me =RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))+0 and be able to pick up also anythign withought a space. The new formula that you gave me will not pick up the different variations ..

[Jonmo1]

Sorry, it does for me.

I can't ammend the first formula to account for a cell without spaces.
The 2nd formula accounts for a cell without spaces.
It basically looks for the first number in the cell, and returns everything from that point on.

Post a new workbook with samples of ALL possible scenarios of your data.

[djmatok]

Perfect!!! Now it works I did not change the cell reference properly. THank you!!!!!!!!

[Jonmo1]

You're welcome.