how to extract decimal numbers from alphanumeric strings in Excel

G

Guest

How do I extract decimal numbers from alphanumeric strings to perform
calculations on the number, eg 25.1 km or 100 m.
 
D

Domenic

Try...

=LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

....where A1 contains your alpha-numeric string.

Hope this helps!
 
G

Guest

Hi,

Use the formula,

=1*LEFT(A1,FIND(" ",A1)-1)

The formula should work if the alphanumeric strings are of the format you
shown in your examples, i.e., number and unit separated by a space.

Regards,
B. R. Ramachandan
 
G

Guest

Many thanks works a treat

Old Tone

B. R.Ramachandran said:
Hi,

Use the formula,

=1*LEFT(A1,FIND(" ",A1)-1)

The formula should work if the alphanumeric strings are of the format you
shown in your examples, i.e., number and unit separated by a space.

Regards,
B. R. Ramachandan
Click to expand...
 
G

Guest

how can i do this but with two numbers

Eg, 16m 30s

This is 16 minutes 30 seconds.

and if you know this, how can i then turn this to read a a decimal, ie,
change 16m 4s to read 16.5

Tim
 
D

Don Guillett

try
to get 16:30
=TEXT(LEFT(D2,FIND("m",D2)-1)&":"&MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2)),"hh:mm")
to get 16.5
=LEFT(D2,FIND("m",D2)-1)&TEXT(MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2))/60,".#")
 
G

Guest

Great Thanks!



Don Guillett said:
try
to get 16:30
=TEXT(LEFT(D2,FIND("m",D2)-1)&":"&MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2)),"hh:mm")
to get 16.5
=LEFT(D2,FIND("m",D2)-1)&TEXT(MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2))/60,".#")
Click to expand...
 
G

Guest

The formula to remove the text from the boxes works a treat, but is there a
way that the results show as a number and not text so i can SUM the numbers
up?

Tim
 
G

Guest

Yes everything works well, it gives me the results, but i wanted to be greedy
and have them so i can sum them up.
 
D

Don Guillett

This formula, with the cell formatted as general, did result in 16.5. In
another =e2*2 gave 33. Please explain.
=LEFT(D2,FIND("m",D2)-1)&TEXT(MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2))/60,".#")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Extract Text from decimal separated string 6
Extracting 7 digit number from alphanumeric string 10
Extracting numbers from alphanumeric string 1
How to extract decimal numbers e.g. $1.57 from alphanumeric string 7
Extract only numbers from an alphanumeric field in Excel? 2
Extracting numbers from alphanumeric data 4
Excel 4 characters alphanumeric data validation check on one excel column. 1
SUMIF with alphanumeric criteria range question 3

Top