| 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
|
|||
|
|||
|
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. |
|
#4
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. |
|
#5
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. |
|
#6
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. |
|
#8
|
|||
|
|||
|
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. |
|
#9
|
|||
|
|||
|
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. |
| 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) | |||
Hybrid Mode
