O
Old Tone
How do I extract decimal numbers from alphanumeric strings to perform
calculations on the number, eg 25.1 km or 100 m.
calculations on the number, eg 25.1 km or 100 m.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
how to extract decimal numbers from alphanumeric strings in Excel
D
Domenic
Try...
=LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))
....where A1 contains your alpha-numeric string.
Hope this helps!
=LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))
....where A1 contains your alpha-numeric string.
Hope this helps!
B
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
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
O
Old Tone
Many thanks works a treat
Old Tone
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
O
Old Tone
Many thanks. It looks a bit fearsome but works fine.
Old Tone
Old Tone
T
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
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,".#")
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,".#")
T
Tim
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,".#")
D
Don Guillett
glad to help
T
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
way that the results show as a number and not text so i can SUM the numbers
up?
Tim
D
Don Guillett
Did you try it before posting?
T
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.
and have them so i can sum them up.
D
Domenic
Try adding...
+0
....at the end of the formula.
Hope this helps!
+0
....at the end of the formula.
Hope this helps!
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,".#")
another =e2*2 gave 33. Please explain.
=LEFT(D2,FIND("m",D2)-1)&TEXT(MID(D2,FIND(" ",D2),FIND("s",D2)-FIND("
",D2))/60,".#")