?
Solved

Extract numbers from strings formula in excel

Posted on 2010-09-20
8
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

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

Accepted Solution

by:
StephenJR earned 1000 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 800 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 200 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

770 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