How to convert 120608 in Excel into 12 Feet 6 inches 8 sixteenths

[Trussman]

I use Excel formulas in the Truss manufacturing industry. (We use a format to
enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )

How can I convert this into a decimal number for calculations? Or how can
this be separated into three cells for manipulation?

Thanks

 

[Roger Govier]

Hi

Provided you always use double digits for each measurement (and assuming
lengths don't exceed 99 feet) then

=TEXT(LEFT(A1,2),"#0")&" feet "
&TEXT(MID(A1,3,2),"#0")&" inches "
&TEXT(RIGHT(A1,2),"#0")&" sixteenths"

 

[Carim]

Hi,

With your input in cell A1 :

1. Feet =VALUE(LEFT(A1,2))
2. Inches =VALUE(MID(A1,3,2))
3. Sixteenths =VALUE(RIGHT(A1,2))

HTH
Cheers
Carim

 

[Ron Coderre]

Maybe something like this:

With a value in A1 of the format FFIISS
Where
FF = feet
II = inches
SS = sixteenths of an inch

This formula returns the decimal equivalent of that number in FEET
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12

Alternatively, this formula returns the decimal equivalent of that number in
INCHES
B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16

For 120608
The first formula returns: 12.54166667
The second formula returns: 150.5

Am I on the right track here?

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Bernard Liengme]

You could use Data | Text to Columns to separate 120608 into three cells
with 12, 06 and 08, respectively
OR
1. Feet =--LEFT(A1,2)
2. Inches =--MID(A1,3,2)
3. Sixteenths =--RIGHT(A1,2)
If you want the values in feet units, then
1. Feet =--LEFT(A1,2)
2. Inches =--MID(A1,3,2)/12
3. Sixteenths =--RIGHT(A1,2)/(12*16)
To get one cell with the value in feet:
=--LEFT(A1,2)+--MID(A1,3,2)/12+--RIGHT(A1,2)/(12*16)

best wishes

 

[Trussman]

Roger, thanks so much for responding. I tried the formula, but 10907 yields
10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The real
trouble, though, remains that the result isn't a decimal format to be used in
another formula. ie. adding two lengths together, or calculating a pitch.

 

[Bernard Liengme]

Your example 120608 suggest you used two digits for inches.
What would 2 ft 0 inch and 5 sixteenths be coded as? 205 ?
We need to know as much as possible about the way data is entered if we are
to be of any help

 

[Trussman]

Sorry. You guys are SO close. The format is that used in the truss program we
use from MiTek Industries. Their expanation:
"Understanding the MiTek F-I-S Rules
Dimensions are entered in dialog boxes using feet, inches, and sixteenths,
or in decimal feet without any special characters such as dashes or commas.
280308
28 03 08
Feet inches sixteenths

28 feet
3 inches
8/16th's
Examples:
360000 = means 36 ft, 00 inches, 00 sixteenths
250408 is 25-4-8
400 is four inches (0-4-0)
3 is three sixteenths
7267 would be 72 inches and 67 sixteenths

A span of twenty-eight and one-half feet would be entered as: 280600 or 28.5
A three and a half inch bearing is 308
A two-foot overhang is either 20000, 2400 or 2.0
A quarter inch butt cut is 4 (NOT .25 - .25 would be 3 inches)
A six-inch bearing width could be 600"

I hope this helps. You guys are so close. Ron Coderre's suggestion is
dead-on except when the sixteenths exceed 9. (Formula ignores the "1" in 10)
Otherwise it's perfect. Your suggestion is works if there are exactly 6
digits.

Thanks,
Trussman

 

[Trussman]

Thanks, Ron. You are totally on track. Refer to the posted reply to Bernard
above. Your formula does exactly what I need unless the sixteenths exceed
9/16". It ignores the second digit from the right. (ie. 12 equals 1/8"
instead of 12/16 or 3/4".)

Maybe something like this:

With a value in A1 of the format FFIISS
Where
FF = feet
II = inches
SS = sixteenths of an inch

This formula returns the decimal equivalent of that number in FEET
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12

Alternatively, this formula returns the decimal equivalent of that number in
INCHES
B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16

For 120608
The first formula returns: 12.54166667
The second formula returns: 150.5

Am I on the right track here?

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I use Excel formulas in the Truss manufacturing industry. (We use a format to
enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )

How can I convert this into a decimal number for calculations? Or how can
this be separated into three cells for manipulation?

Thanks

 

[Bernie Deitrick]

I think Ron simply left out a zero in the last MOD:

=INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12

Should be

=INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,100)/16)/12

 

[Roger Govier]

Hi

I did say that provided each measurement was double digit.
100907 would have yielded the correct result.

If you want to add the data, then you will first need to split it into
its components.
Again, all measurements must be a 6 character string with double digit
for each of the component measurements.
In B1
=IF(A1="",0,--LEFT(A1,2))
in C1
=IF(A1="",0,--(MID(A1,3,2)))
in D1
=IF(A1="",0,--(RIGHT(A1,2)))
copy down as appropriate

in B6
=SUM(B1:B5)+INT((SUM(C1:C5)+INT(SUM(D15)/16))/12)
in C6
=MOD(SUM(C1:C5)+INT(SUM(D15)/16),12)
in D6
=MOD(SUM(D15),16)

in A6
=TEXT(B6,"00")&text(C6,"00")&TEXT(D6,"00")

 

[Trussman]

Thanks to you all for solving this problem! It's doing exactly what I want it
to.

 

[Ron Coderre]

Thanks for the feedback.....I'm glad you could work with that.

....and thanks for catching the typo in my formula.
You're right about the sixteenths.

Instead of FEET:
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
and INCHES:
B1: =INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16

The formulas SHOULD BE:
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,100)/16)/12
and
B1: =INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,100)/16
for FEET and INCHES, respectively.


***********
Regards,
Ron

XL2002, WinXP

Thanks, Ron. You are totally on track. Refer to the posted reply to Bernard
above. Your formula does exactly what I need unless the sixteenths exceed
9/16". It ignores the second digit from the right. (ie. 12 equals 1/8"
instead of 12/16 or 3/4".)

Maybe something like this:

With a value in A1 of the format FFIISS
Where
FF = feet
II = inches
SS = sixteenths of an inch

This formula returns the decimal equivalent of that number in FEET
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12

Alternatively, this formula returns the decimal equivalent of that number in
INCHES
B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16

For 120608
The first formula returns: 12.54166667
The second formula returns: 150.5

Am I on the right track here?

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I use Excel formulas in the Truss manufacturing industry. (We use a format to
enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )

How can I convert this into a decimal number for calculations? Or how can
this be separated into three cells for manipulation?

Thanks