Solved

Extract numbers from strings formula in excel

Posted on 2010-09-20
8
280 Views
Last Modified: 2012-05-10
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
Comment
Question by:luisr69
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 24

Expert Comment

by:StephenJR
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

by:barry houdini
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

by:luisr69
ID: 33717442
It returns the first 5-number:12345 without texts.
Why is "1*MID" if you don't mind my asking.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Accepted Solution

by:
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

by:barry houdini
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

by:Rodney Endriga
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

by:tilsant
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question