Extract numbers from strings formula in excel

Hi Experts:

Let's say I have a field with the string value = ABC 12345123451
I need to extract the number out of that field, just the 11-number and leave the text.
What is the solution for this with formula not VBA?
Thanks.
luisr69Asked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
Sorry, this is an alternative, but if always the same format barry's approach looks simpler.

=LOOKUP(99^99,--(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)+1)))))
0
 
StephenJRCommented:
Try this, enter it with Ctrl+Shift+Enter so curly brackets appear round it

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
0
 
barry houdiniCommented:
It depends on the consistency of your data. If there are always 11 digits at the end try
=RIGHT(A1,11)+0
If that isn't the case then please post some representative examples
regards, barry
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
luisr69Author Commented:
It returns the first 5-number:12345 without texts.
Why is "1*MID" if you don't mind my asking.
0
 
luisr69Author Commented:
barry,
Yes it will always be 11-digit.
Thanks.
0
 
barry houdiniConnect With a Mentor Commented:
Does that work for you then? If there are any leading zeroes then my suggested formula will lose them (because +0 converts to numeric). If you want to retain leading zeroes use just
=RIGHT(A1,11)
The result will be a text-formatted number
regards, barry
0
 
Rodney EndrigaConnect With a Mentor Data AnalystCommented:
If your data is standardized, then you can use the following:
=RIGHT(A1,11)
This will pull all the information from the Right side of the cell that is 11-characters long.

If it is not standardized, you can use something like:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
This will grab everything to the Right side of the " " (space character). No matter what the length of the numbers. You can replace the space character in the formula to another character, if needed (i.e. ~, |, .)

For both cases, you just need to reference the proper cell. Change the A1 to wherever your data is located.
0
 
tilsantCommented:
And if the data is v.consistent and of the form
<string> <space> <number>

Then u can select that column and then go to Data >> Text to Columns >> Delimited >> Use "Space" as the separator.


HTH
Tils.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.