In Google Sheets, how do we take a decimal feet value and turn it into properly formatted fractional Feet & Inches?

Question

Been looking all over and nothing comes up as far as a Google Sheets formula.

Let's say we have a value of 3.6875 feet. We can use the number format # ??/??, but it will give us a fractional value in feet (in this case, 3 11/16 feet).

How do we "grab" the 11/16 and multiply it by 12 to get the inches value (8.25), and then the really tricky part, how do we display the whole enchilada as 3'8¹/⁴" (yes, including the superscript)?

Answer 1

A1= 3.6875

B1=INT(A1)&"'-"&TRIM(TEXT(ROUND(MOD(A1,1)*12*16,0)/16,"# ??/??")&"""")

Output: 3'-8 1/4 "

UPDATED:

You can have a table to search the superscript

The idea to get the superscript: with above output (3'-8 1/4"): is to extract the fraction (1/4), search for the equivalent superscript in the table (¹/⁴), then replace it (3'-8 ¹/⁴"):

So basically we will need:

• REGEXEXTRACT
• VLOOKUP
• REGEXREPLACE

SPREADSHEET DEMO: HERE

 =arrayformula(
  if(len(A2:A),INT(A2:A)&"'-             
     "&REGEXREPLACE(TRIM(TEXT(ROUND(MOD(A2:A,1)*12*16,0)/16,"#??/??")&""""),
        "\d{1}\/\d+",
            VLOOKUP(IFNA(
    REGEXEXTRACT(TRIM(TEXT(ROUND(MOD(A2:A,1)*12*16,0)/16,"# ??/??")&""""),
    "\d{1}\/\d+"),
    "0/0"),
            TABLE!A:B,2,0)),""))