Extract Only Numbers From Text String

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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

Active Member
Joined
Feb 1, 2009
Messages
303
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,307
With:
A1: 91a28ABC3712DEF465

This array formula that pulls whichever sequential number you specify:
C1: (the ordinal of the embedded number to pull....eg 2)
Code:
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

Active Member
Joined
Feb 1, 2009
Messages
303
Ron, yes it works great. Is the maximum numbers of characters 25 from the 1st number?
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,307
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

Active Member
Joined
Feb 1, 2009
Messages
303
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,307
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:
Code:
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

Active Member
Joined
Feb 1, 2009
Messages
303
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
 

Forum statistics

Threads
1,082,335
Messages
5,364,686
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top