Thread: Extract Only Numbers From Text String Thanks: 0 Likes:  3 Post #2195554 (1)Post #2195604 (1)Post #2194891 (1)

1. Re: Extract Only Numbers From Text String Originally Posted by Ron Coderre I think we finally have an all-purpose formula.
Greetings Ron,

I found this information quite useful solving an issue I had. However, I have run into som problems.
I am using the formula below extracting numbers from a string formated something like this: NUMBER-NUMBER TEXT TEXT (TEXT) NUMBER
However, when the first bracket of texts first three letters resembles that of a month (example given: MARCONIAN) the formual returns a date in number value (41712) and not the actualy two-digit number.

I expect the formual need to change format somehow, but I fail to locate how/where.
I have tried altering format in both the source and the target cell.

(Formula is translated to localized language, the original formula below)

=IFERROR(LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(SUBSTITUTE(" "&A1," ","|"),ROW(\$1:\$25),1)))=0)*ISNUMBER(--(MID(SUBSTITUTE(" "&A1," ","|"),ROW(\$2:\$26),1)))),ROW(\$2:\$26)),C1),ROW(\$1:\$25))),””)  Reply With Quote

2. Re: Extract Only Numbers From Text String

Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.  Reply With Quote

3. Re: Extract Only Numbers From Text String Originally Posted by István Hirsch Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.
No, they are not. Due to sloppy input there are spaces (" ") in random locations on a number of the entries.

We have looked into various solutions for this, but the amount of data is vast, and thus far this is the only formula that extracts the numbers with a relative precision.

The only information I am interessted in is recorded like this (where X, Y and Z being number values) XXX-YY ZZZZ. The entry can contain any number of spaces and or characters anywhere in the cell, however it will never split the number values.

Alternatively I could return all 3-digit numbers in first column, then return all 2-digit numbers in second and last all 4-digit numbers in last column, but I would not know how to accomplish this.  Reply With Quote

4. Re: Extract Only Numbers From Text String Originally Posted by Ron Coderre I think we finally have an all-purpose formula.

With A1:A7 containing
Code:
45t*&65/
9128A+BC37/E*465
91a28ABC3712DEF465
91.28ABC3712DEF465
91.28ABC37.1D2F465
464.59 DDFSDF 23.25 ccd 157.25
123asdf.asdf.asdf456

and
C1: 2
This array formula returns the specified number from the string,
Code:

B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(
SUBSTITUTE(" "&A1," ","|"),ROW(\$1:\$25),1)))=0)*ISNUMBER(--(MID(
SUBSTITUTE(" "&A1," ","|"),ROW(\$2:\$26),1)))),ROW(\$2:\$26)),C1),ROW(\$1:\$25)))
Copy B1 and paste into B2:B7

With the above examples, the formulas return these values:
Code:
45
37
28
91.28
2
23.25
456
Note: If you want to display two decimal places, change the number format.

Are we done, yet?
This formula worked like a charm for me, it made me create an account on the forum just to thank you guys who created it!

Excel 2013 Win7  Reply With Quote

5. Re: Extract Only Numbers From Text String

Will this formula work for an alphanumeric string longer than 14 characters? If not, are you aware of any function that will provide only the numerical values from the following string?

 2297244910LA172/165

I'd like for the function to return a value of 2297244910172165 in the example above.

Tks. dhaile

========================================= Originally Posted by Ron Coderre Got it! I understand...evidently, the last coffee molecule just bumped into my brain.

This formula returns the second sequence of numbers from a string in the format of: digits letters digits
Code:

B1: =LOOKUP(99^99,--("0"&MID(MID(A1,LEN(LOOKUP(10^99,--LEFT(A1,ROW(\$1:\$25)
)))+1,25),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A1,LEN(LOOKUP(10^99,--LEFT(A1,
ROW(\$1:\$25))))+1,25)&"0123456789")),ROW(\$1:\$25))))
I'm hoping that can be shortened.

That formula returns these results:
For: 45t*&65/
result: 65

For: 9128A+BC37/E*465
result: 37

Does that help?  Reply With Quote

6. Re: Extract Only Numbers From Text String

Hello Experts,
With regarding to this post i have very simple query in front if you, hope will get great help.
very simple--
abc123
123abc
a123b
12bnm12
a12bn
Looking for only numbers in next cell. Will this possible. if yes, please post the help.

[QUOTE=Ron Coderre;2193647]Try this:  Reply With Quote

7. Re: Extract Only Numbers From Text String

Use this :
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))  Reply With Quote

8. Re: Extract Only Numbers From Text String

Excellent... Appriciate your brain power Originally Posted by yesterdays Use this :
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))  Reply With Quote

9. Re: Extract Only Numbers From Text String

Hi, I have searched this thread but so far none of the formulas are working. Perhaps I am to new to excel and have an error in cut/paste? Anyways I am looking for a formula to extract only the numbers. So for example a part number BROTHER HL-2270DW and apply formula so output is only "2270". Any suggestion?  Reply With Quote

10. Re: Extract Only Numbers From Text String Originally Posted by Brusky Hi, I have searched this thread but so far none of the formulas are working. Perhaps I am to new to excel and have an error in cut/paste? Anyways I am looking for a formula to extract only the numbers. So for example a part number BROTHER HL-2270DW and apply formula so output is only "2270". Any suggestion?
The formula posted in Message #37 works for me.  Reply With Quote

User Tag List

Tags for this Thread

extract numbers string  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•