Solved

Extract numbers from strings formula in excel

Posted on 2010-09-20
8
279 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…

777 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