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.

 

[schielrn]

It looks I should definitely consider upgrading to Excel 2007. Probably, exceeding 7 nested brackets. How would I make this work with Excel 2003? Can I separate it into two columns?

Yes you can separate into separate columns is what most people do (or modify your formula to not need 7 ifs) or you can put them within named ranges.

 

[pto160]

Ron. Thanks. The formula you gave me is totally robust and dynamic. It picks up the 2nd number no matter the text surrounding it, even including no spaces between the text and the number?

If I wanted to get the nth number or the third number, does this require a total rewrite of the formula?

 

[Domenic]

9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string

As you've already indicated, the numbers extracted by the first MID function are multiplied by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for the extracted numbers.

Note that the first extracted number is multiplied by the first number in the array 10^ROW(INDIRECT(...)), which is 10^1 or 10. Therefore, if the first number extracted by MID is 4, then 40 would be returned instead of 4. The second extracted number would then be multiplied by 10^2, the third by 10^3, and so on. Hence, we divide by 10 at the end prior to summing.

However, we could avoid dividing by 10 if we use the following instead...

10^(ROW(INDIRECT("1:"&LEN(A2)))-1)

Now, the first multiplier is 10^0 or 1 instead of 10^1 or 10. So if the first number extracted by MID is 4, then 4 would be returned, not 40. Then there would be no need to divide by 10.

 

[Ron Coderre]

With:
A1: 91a28ABC3712DEF465

This array formula that pulls whichever sequential number you specify:
C1: (the ordinal of the embedded number to pull....eg 2)

B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--MID("|"&A1,
ROW($1:$25),1))=0)*ISNUMBER(--MID("|"&A1,ROW($2:$26),1))),ROW($2:$26)),C1),
ROW($1:$25)))

In the above example, the formula returns the 2nd number in that string:
28

If you change C1 to 3...the formula returns: 3712

Does that help?

 

[pto160]

Ron, yes it works great. Is the maximum numbers of characters 25 from the 1st number?

 

[mgirvin]

Thank you very much, Domenic and Ron!!!

 

[Ron Coderre]

Ron, yes it works great. Is the maximum numbers of characters 25 from the 1st number?

No, 25 is not an upper limit. Sometimes these kinds of formulas are used in
tens of thousands of cells. I just used 25 characters to keep the formula
overhead down. Change the 25's to whatever makes sense for your
situation. Remember to change the 26's to one number higher than your new limit.

 

[pto160]

Thanks Ron. I played around with it and I got it go out 7 numbers. I cannot imagine going beyond that with the dataset that I have. The formula picks up the number no matter the format of the string. Just wondering,

This formula does pick up decimal points, but it treats the number characters before and after the decimal point as 2 separate numbers, so you have to go up in increments of 2 in C1 to pick up the next number.

<title>Excel Jeanie HTML</title>Sheet3

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 492px;"> <col style="width: 74px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>4884.23 464.25 1111500.78 </td> <td style="text-align: right;">464.25</td> <td style="text-align: right;">3</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>4884.23 464.25 1111500.78 </td> <td style="text-align: right;">25.00</td> <td style="text-align: right;">4</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>4884.23 464.25 1111500.78 </td> <td style="text-align: right;">1111500.78</td> <td style="text-align: right;">5</td></tr></tbody></table>
Is this the best way to do it?

I can do this in a separate column like D1. Is there a way to go one character to the left of the result in B1. This will pick up any negative sign, which I can then concatenate with B1.
Great formula.

Excel tables to the web >> Excel Jeanie HTML 4

 

[Ron Coderre]

It would be so disheartening to learn that your actual cells contained numbers separated by spaces. I hope that is not the case because the formula to pluck out those numbers is so much simpler:

With
A1: 4884.23 464.25 1111500.78
B1: (the segment to return....eg 2)

This formula returns that value:

C1: =LOOKUP(10^99,--LEFT(MID(A1,FIND("|",SUBSTITUTE(" "&A1," ","|",B1)),255),
ROW($1:$15)))

In the above example, the formula returns: 464.25

 

[pto160]

It works great. I will probably use the other formula more because it is more robust. It works with text, gives me the nth number (no separate formula for last and first number and numbers beside text with no spaces.

Is there a way to change a number from 0 to 2 decimal points?
Usually with some ERP systems, numbers like 464.00 that are exported into Excel are converted to 464.

<title>Excel Jeanie HTML</title>Sheet3

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 235px;"> <col style="width: 254px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td> </td> <td>Result</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>CC 464 C 1111500.78 hghghgh 784.24</td> <td>CC 464.00 C 1111500.78 hghghgh 784.24</td></tr></tbody></table>
This means it will always go up by increments of two in C1.
Let me know if this should be a separate thread.
Excel tables to the web >> Excel Jeanie HTML 4