Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to
split out so I have the 6 (feet) in one column and the fractional inches (3 3/8) converted in the next column to 3.375, etc. Below is some sample data: 4'-1 3/8" to 4 | 1.375 9'-2 1/2" to 9 | 2.500 7'-3 1/8" to 7 | 3.125 Any help is greatly appreciated as these are weekly data conversion needs. Thanks! -- Marv Lusk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
Hi Marv:
I split the task up into four seperate parts as it is easier to handle: B5=4'-1 3/8" C5=LEFT(B5,FIND("'",B5,1)-1) = 4 D5=MID(B5,FIND("'",B5,1)+2,FIND(" ",B5,1)-FIND("'",B5,1)-2) = 1 E5=MID(B5,FIND(" ",B5,1)+1,FIND("/",B5,1)-FIND(" ",B5,1)-1) =3 F5=MID(B5,FIND("/",B5,1)+1,FIND("""",B5,1)-FIND("/",B5,1)-1) =8 This is not a perfect solution as it does not deal with the problems of errors in the data and the numbers with no inches nor factions of inches. But it will get you going. I would personally put it in macro, it is a lot easier to manage. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "MarvInBoise" wrote: I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to split out so I have the 6 (feet) in one column and the fractional inches (3 3/8) converted in the next column to 3.375, etc. Below is some sample data: 4'-1 3/8" to 4 | 1.375 9'-2 1/2" to 9 | 2.500 7'-3 1/8" to 7 | 3.125 Any help is greatly appreciated as these are weekly data conversion needs. Thanks! -- Marv Lusk |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
If 6"-3 3/8" is in cell A2
=VALUE(LEFT(A2,FIND("'",A2)-1)) will give the number of feet =MID(A2,FIND("-",A2)+1,FIND(" ",A2)-FIND("-",A2)-1)+MID(A2,FIND(" ",A2)+1,FIND("/",A2)-FIND(" ",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1) will give the number of inches converted to decimal format. "MarvInBoise" wrote: I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to split out so I have the 6 (feet) in one column and the fractional inches (3 3/8) converted in the next column to 3.375, etc. Below is some sample data: 4'-1 3/8" to 4 | 1.375 9'-2 1/2" to 9 | 2.500 7'-3 1/8" to 7 | 3.125 Any help is greatly appreciated as these are weekly data conversion needs. Thanks! -- Marv Lusk |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
Just to make sure the spacing is correct for final formula:
=MID(A2,FIND("-",A2)+1,FIND(" ",A2)-FIND("-",A2)-1)+ MID(A2,FIND(" ",A2)+1,FIND("/",A2)- FIND(" ",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
Thank you for the fast and expert feedback; we'll give it a shot!
-- Marv Lusk "BoniM" wrote: If 6"-3 3/8" is in cell A2 =VALUE(LEFT(A2,FIND("'",A2)-1)) will give the number of feet =MID(A2,FIND("-",A2)+1,FIND(" ",A2)-FIND("-",A2)-1)+MID(A2,FIND(" ",A2)+1,FIND("/",A2)-FIND(" ",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1) will give the number of inches converted to decimal format. "MarvInBoise" wrote: I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to split out so I have the 6 (feet) in one column and the fractional inches (3 3/8) converted in the next column to 3.375, etc. Below is some sample data: 4'-1 3/8" to 4 | 1.375 9'-2 1/2" to 9 | 2.500 7'-3 1/8" to 7 | 3.125 Any help is greatly appreciated as these are weekly data conversion needs. Thanks! -- Marv Lusk |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert 6'-3 3/8" to usable numbers
On Tue, 11 Mar 2008 06:58:02 -0700, MarvInBoise
wrote: I have a large spredsheet of data that comes in like 6'-3 3/8" that I need to split out so I have the 6 (feet) in one column and the fractional inches (3 3/8) converted in the next column to 3.375, etc. Below is some sample data: 4'-1 3/8" to 4 | 1.375 9'-2 1/2" to 9 | 2.500 7'-3 1/8" to 7 | 3.125 Any help is greatly appreciated as these are weekly data conversion needs. Thanks! Feet: =--LEFT(A1,FIND("'",A1)-1) Inches: =--MID(A1,FIND("-",A1)+1,FIND("""",A1)-FIND("-",A1)-1) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert numbers (ex. "4") to text (ex. "four") | Excel Discussion (Misc queries) | |||
How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric? | Excel Worksheet Functions | |||
how to make a checkbox usable for users to "check" it? | Excel Discussion (Misc queries) | |||
convert "15000" to "Fifteen thousand" in excel cell? | Excel Worksheet Functions | |||
how can I convert scanned data into usable numbers in excel? | Excel Discussion (Misc queries) |