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

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

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.

Harry  Register To Reply

2. ## Re: Need code to pull numeric data out of an alphanumeric string (string not constant)

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  Register To Reply

3. ## Re: Need code to pull numeric data out of an alphanumeric string (string not constant)

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.
Formula:  `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.  Register To Reply

4. ## Re: Need code to pull numeric data out of an alphanumeric string (string not constant)

Worked perfectly.

Thanks!  Register To Reply