how to extract decimal numbers from alphanumeric strings in Excel

[Old Tone]

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

 

[Domenic]

Try...

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

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

Hope this helps!

 

[B. R.Ramachandran]

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

 

[Old Tone]

Many thanks works a treat

Old Tone

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

 

[Old Tone]

Many thanks. It looks a bit fearsome but works fine.

Old Tone

 

[Tim]

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

 

[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,".#")

 

[Tim]

Great Thanks!

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,".#")

 

[Don Guillett]

glad to help

 

[Tim]

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

 

[Don Guillett]

Did you try it before posting?

 

[Tim]

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

 

[Domenic]

Try adding...

+0

....at the end of the formula.

Hope this helps!

 

[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,".#")