|
how to extract decimal numbers from alphanumeric strings in Excel
How do I extract decimal numbers from alphanumeric strings to perform
calculations on the number, eg 25.1 km or 100 m. |
Try...
=LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
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" wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
Many thanks works a treat
Old Tone "B. R.Ramachandran" wrote: 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" wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
Many thanks. It looks a bit fearsome but works fine.
Old Tone "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
Great Thanks!
"Don Guillett" wrote: 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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
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" wrote: glad to help -- Don Guillett SalesAid Software "Tim" wrote in message ... Great Thanks! "Don Guillett" wrote: 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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
Did you try it before posting?
-- Don Guillett SalesAid Software "Tim" wrote in message ... 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" wrote: glad to help -- Don Guillett SalesAid Software "Tim" wrote in message ... Great Thanks! "Don Guillett" wrote: 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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
Yes everything works well, it gives me the results, but i wanted to be greedy
and have them so i can sum them up. "Don Guillett" wrote: Did you try it before posting? -- Don Guillett SalesAid Software "Tim" wrote in message ... 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" wrote: glad to help -- Don Guillett SalesAid Software "Tim" wrote in message ... Great Thanks! "Don Guillett" wrote: 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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
how to extract decimal numbers from alphanumeric strings in Ex
Try adding...
+0 ....at the end of the formula. Hope this helps! In article , Tim wrote: 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 |
how to extract decimal numbers from alphanumeric strings in Ex
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,".#") -- Don Guillett SalesAid Software "Tim" wrote in message ... Yes everything works well, it gives me the results, but i wanted to be greedy and have them so i can sum them up. "Don Guillett" wrote: Did you try it before posting? -- Don Guillett SalesAid Software "Tim" wrote in message ... 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" wrote: glad to help -- Don Guillett SalesAid Software "Tim" wrote in message ... Great Thanks! "Don Guillett" wrote: 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 SalesAid Software "Tim" wrote in message ... 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 "Domenic" wrote: Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article , "Old Tone" <Old wrote: How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. |
| All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com