Extract Numbers and Decimals from Text

**PY_** · 11-12-2010, 03:31 PM

I have a handfull of different text layouts that i need to extract the numbers along with their decimals from. The text layouts change so using a generic code to just extract the numbers+decimals seems the way to go.

I found a code here that will amost work:
http://www.mrexcel.com/forum/showthread.php?t=362184

Using this Formula and CTRL+Shift to apply it:
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Using the above, the following gets converted like this:
6.141 ft by 40.552 ft - Results in 614140552

The problem is, i want to be able to seperate out the numbers by a space and i do not want to lose the decimal point. Any idea how i can do that?

Change This:
6.141 ft by 40.552 ft
To This:
6.141 40.552

Change This:
4.188 ft by 25 ft at 13.54 ft
To This:
4.188 25 13.54

I can take the result and split them out as needed into different cells after that.

**martindwilson** · 11-12-2010, 03:43 PM

have you tried text to columns instead using space as delimiter, depends on your exact variations but you can often remove a loads of stuff then use simpler functions after to sort things out ,your 2 examples suit this method when done together no functions needed at all.

**Ron Coderre** · 11-12-2010, 03:59 PM

With these values in A2:A3...
6.141 ft by 40.552 ft
4.188 ft by 25 ft at 13.54 ft

and
B1: 1
C1: 2
D1: 3

In Excel 2007 or later...
Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of just ENTER) in

Please Login or Register  to view this content.

Copy B2 and Paste into C2:D2
Copy B2:D2 and Paste into B3:D3

These will be the returned values:

Please Login or Register  to view this content.

If you have Excel 2003, strip the IFERROR function from the formula.
(it'll then return an error if there's no 2nd or 3rd number in the string)

Is that something you can work with?

**squiggler47** · 11-12-2010, 04:00 PM

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"ft",""),"by",""),"at",""))

**PY_** · 11-12-2010, 04:03 PM

I looked into that but seperating by a space really makes a mess of things because it is scattered out quite a bit. I need to perform this on many lines of text and use the text in calculations so the simpler i can make it the better.

I tried a handfull of MID and SEARCH formulas but not very successful. Some i was able to extract correctly, others still had spaces in the numbers, and if it had 3 numbers in it i was unable to figure out how to extract that at all.