| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My numbers format as a number with two decimal places. | Excel Discussion (Misc queries) | |||
| Formula without using numbers after decimal in the answer | Excel Discussion (Misc queries) | |||
| Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
| Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
| extract numbers, convert to date | Excel Discussion (Misc queries) | |||
Linear Mode
