Need code to pull numeric data out of an alphanumeric string (string not constant)

[harrydnyc]

Hello Excel Gurus,

Need your help with pulling some numeric data out of a string with alphanumeric text. I am having trouble using a command like "RIGHT" because the size and format of the string is not constant. What I mean is that the number of characters prior to or preceeding the data I need is not always the same. I still need the non-numeric text, I just need it seperated into a different column.

So, what I really would need is the following in the attached spreasheet.

1) In column B, I need the actual rate, which is only the NUMERIC information contained in column B already.
2) In column C, I need all the NON-NUMERIC text that proceeds the NUMERIC information that is currently in column B.
3) All information that follows the NUMERIC information in column B can be discarded.

So, right now, this is and example of what you see in column B - Variable 0.03947 kwh

What I would need is something like this:

Column B: 0.03947
Column C: Variable

Any help would be GREATLY appreciated.

Thanks for all your help.

Harry

[AndyLitch]

In C2... =IF(B2<>"",MID(B2,(FIND(" ",TRIM(B2),1))+2,(FIND(" ",TRIM(B2),(FIND(" ",TRIM(B2),1))+1))-(FIND(" ",TRIM(B2),1))),"")
In D2....=IF(B2<>"",LEFT(B2,FIND(" ",TRIM(B2),1)+1),"")

Copy C2:D2 and paste down through the table

[Marcol]

Your profile states 2003, but your sample is 2007 or above.

This should work for either.

1/. Insert two columns after Column B "Rate"

2/. Put this formula in C2, Drag across to D2, then down as required.

Please Login or Register  to view this content.

3/. Copy Columns C:D and paste special > Values if required.

This trims the results and returns a number rather than a string for your rates.

[harrydnyc]

Worked perfectly.

Thanks!