What is the custom format cell type I need to create to make cells in inches (such as 0.125, .25, .375, 3.125) into the format of 00000/0 where the 0/0 is 1/8 of an inch? So, 4.25 would become 00042/8. 62.25 inches would become 05022/8. Thanks.
Measurement Conversion Format Cell


Re: Measurement Conversion Format Cell
Try
00000/8

Re: Measurement Conversion Format Cell
It needs to know to go from 00117/8 to 010000. etc.

Re: Measurement Conversion Format Cell
doyleday,
A custom format of "00000/8" will get you a fraction for the last two places and the inches to the left in the first 00 place but, I don't think there is a way to tell Excel to only go up to 12 and then use feet to the left in another 00 place. Can you explain more about why such a formatting is desirable? For example, if you could use a formula or not?
Jim

Re: Measurement Conversion Format Cell
As Jim says, I don't think you can do that with formatting alone. If you have your inches in A1, e.g. 62.25 then you could use a formula in another cell to give you 05022/8. Try
=TEXT(INT(A1/12),"00")&TEXT(MOD(A1,12),"000/8")


Re: Measurement Conversion Format Cell
Essentially, we had an old program that created a column with heights in the format 00000/? which goes feet/inches/ then 1/8 inch, 1/16, etc. Our new program gives the heights in a number format that is just inches. So instead of giving 04051/8 for 4ft and 5 and 1/8 inches our new program gives 53.125. Some clients prefer to see it in ftinand 0/?. A formula would be fine if this is not possible to do by just formatting the cell. Hope that helps, let me know if things are still unclear. Thanks.
That formula is perfect. Thanks a ton.

Re: Measurement Conversion Format Cell
Note: if you always have your decimal inches in eighths, e.g. 22.375 or 20.75 then the above formula is fine but if you have some values like 59.99 it may give odd results in which case amend to
=TEXT(INT(ROUND(A1*2/3,0)/8),"00")&TEXT(MOD(ROUND(A1*8,0)/8,12),"000/8")

Re: Measurement Conversion Format Cell
How can I change this formula to round to the nearest inch while still keeping the 0/0? For example 49.25 would become 04010/0. For some reason some clients want this format.

Re: Measurement Conversion Format Cell
Also, I just realized there are times when the original number is in feet (so 45.57 = 45 ft and .57 inches) How do I convert this to the format ftin0/0? Thanks again for all the help so far.

Re: Measurement Conversion Format Cell
doyleday,
You may want to insert a helper column that contains a value for each customer related to how they want the value displayed. For example the value 1 could represent display it like this "00000/8", 2 could represent "00Ft. 00In", etc. Then you can write the formula to display the results as an IF statement like so:
=IF($A$2=1,TEXT(INT(ROUND(A1*2/3,0)/8),"00")&TEXT(MOD(ROUND(A1*8,0)/8,12),"000/8","the other format")
Jim
PS  Convert feet to inches by multiplying by 12.


Re: Measurement Conversion Format Cell
Quote from doyledayHow can I change this formula to round to the nearest inch while still keeping the 0/0? For example 49.25 would become 04010/0. For some reason some clients want this format.
Try
=TEXT(INT(ROUND(A1,0)/12),"00")&TEXT(MOD(ROUND(A1,0),12),"00")&"00"
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!