How to convert 6'-3 3/8" to usable numbers

  • Thread starter Thread starter MarvInBoise
  • Start date Start date
M

MarvInBoise

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!
 
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.
 
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.
 
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)
 
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!
Click to expand...

Feet:

=--LEFT(A1,FIND("'",A1)-1)

Inches:

=--MID(A1,FIND("-",A1)+1,FIND("""",A1)-FIND("-",A1)-1)

--ron
 
Back
Top