How to convert inches with decimals to feet and inches

Question

I have an Excel spreadsheet with the following data in A1 and A2(column is formatted as Text).

  A
1 258.1875
2 294.1875

I need the data to display in B1 and B2 in feet and inches, like this: (column needs to be formatted as Text).

  B
1 21'-6 3/16"     that is 21'-6space3/16"
2 22'-10 3/16"

Can someone provide a formula which will do this?

This is how I'm currently doing this: I'm splitting the inches and the decimals into 2 columns

Example:

258 in column B1        .1875 in Column C1.  I multiply column A1 by 12 to get:
21.5  in B1,            .1875 in column C1

Then I insert a column for the new decimal so I now have`

21 in B1                  .5 in C1          .18875 in D1
We concat with ' in B1    multiply by 12     convert to fraction

Now I have

21" in B1                 6 in C1            3/16 in D1

I concatenate the 3 fields, adding a dash before and a space after the 6, and the inches symbol after the 3/16 in C1 which gives me

21'-6 3/16"

It's a lot of work and I have to do this for up to 1000 lines at a time, so an Excel formula would save me so much time!

Answer 1

Use:

=INT(INT(A1)/12) & "'-" & MOD(INT(A1),12) & " " & TEXT(MOD(A1,1),"??/16") & """"

If you want to omit the fraction if there is none:

=INT(INT(A1)/12) & "'-" & MOD(INT(A1),12) & IF(MOD(A1,1)>0,TEXT(MOD(A1,1)," ??/16"),"") & """"