Searching for a number within a text string

[pauldaddyadams]

Hi, I have a list of customers and account numbers contained within a cell. I need a formula if possible that searches from the right of the cell and then returns the all the numbers.

e.g.

Arsenal1234 Required formula result 1234
Liverpool2456 Required formula result 2456
Chelsea100564 Required formula result 100564

I can not use text to columns as they data is not consistant.

Also worth noting is that the customer numbers vary fro 1 digit to 9 digits long.

Any help would be great

[NBVC]

Try:

Please Login or Register  to view this content.

where A1 contains original string

[pauldaddyadams]

Brilliant, Thank you. If you get two mins could you explain how it works so i know for the future.

[NBVC]

This part does most of the work:

MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))

it basically replaces each digit it finds in the string with a 0, so you will get a result of 9 variations of the text string.. It then adds a 0 to the end of each element so that the Find() function doesn't return an error...

The Find() functions then finds the 0 in each of the elements and reports the position of the first 0 in each element..

The Min() returns the smallest position location which would translate to the earliest point that the Substitute function replaced a digit with a 0.. which is, of course, the first time a digit occurs in the original string....

This Min postion is then subtracted from the Length of the original string and 1 is added to determine how far from the Right to extract from.

This returns a text version of your number... if you want to convert to an actual number add a +0 to the end of the formula.

Note: To follow the evaluation go to Tools|Formula Auditing|Evaluate Formula and keep clicking Evaluate to follow the sequence of events leading to the final result.

[pauldaddyadams]

Thanks for your time once more. I never new about the forumla evaluation tool, it should help me in the future!

[NBVC]

Great. You are welcome.

Can you please mark your thread as Solved?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[dcflondon]

Unfortunately this would appear not to work for cells where there is no "text". If I'm wrong please let me know, as I could then remove the REF! error from my formulae

Best

D

[NBVC]

Perhaps start a new thread indicating your exact situation and samples.... you can link here if you need to....