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?
Who is Participating?
StephenJRConnect With a Mentor Commented:
Sorry, this is an alternative, but if always the same format barry's approach looks simpler.

Try this, enter it with Ctrl+Shift+Enter so curly brackets appear round it

barry houdiniCommented:
It depends on the consistency of your data. If there are always 11 digits at the end try
If that isn't the case then please post some representative examples
regards, barry
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.
luisr69Author Commented:
Yes it will always be 11-digit.
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
The result will be a text-formatted number
regards, barry
Rodney EndrigaConnect With a Mentor Data AnalystCommented:
If your data is standardized, then you can use the following:
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.
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.

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.