Solved

# Extract numbers from strings formula in excel

Posted on 2010-09-20
279 Views
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.
0
Question by:luisr69
• 2
• 2
• 2
• +2

LVL 24

Expert Comment

ID: 33717322
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

LVL 50

Expert Comment

ID: 33717365
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

Author Comment

ID: 33717442
It returns the first 5-number:12345 without texts.
Why is "1*MID" if you don't mind my asking.
0

Author Comment

ID: 33717514
barry,
Yes it will always be 11-digit.
Thanks.
0

LVL 24

Accepted Solution

StephenJR earned 250 total points
ID: 33717540
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

LVL 50

Assisted Solution

barry houdini earned 200 total points
ID: 33717545
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

LVL 5

Assisted Solution

Rodney Endriga earned 50 total points
ID: 33717569
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

LVL 12

Expert Comment

ID: 33717574
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file forâ€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦