Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[NewAtExcel]

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: =HVISFEIL(SLÅ.OPP(10^99;--DELTEKST("|"&J15;N.MINST(HVIS(((--ERTALL(--("0"&DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($1:$60);1)))=0)*ERTALL(--(DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($2:$61);1))));RAD($2:$61));2);RAD($1:$60)));" ")

(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))),””)

 

[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.

 

[NewAtExcel]

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.

 

[elde0618]

I think we finally have an all-purpose formula.

With A1:A7 containing

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,

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:

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

 

[dhaile]

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

<COLGROUP><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><TBODY>
</TBODY>

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


Tks. dhaile

=========================================

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

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?

 

[VBABEGINER]

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.

Try this:

 

[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))

 

[VBABEGINER]

Excellent... Appriciate your brain power

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))

 

[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?

 

[Rick Rothstein]

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.