2

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)?

Shian Han
  • 307
  • 1
  • 5
  • 16

1 Answers1

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

enter image description here

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)),""))

enter image description here

Nabnub
  • 953
  • 6
  • 15
  • Thanks! I did a little more digging regarding the superscript part, and found this: https://infoinspired.com/google-docs/spreadsheet/how-to-get-subscript-and-superscript-numbers-in-google-spreadsheets/ But can't figure out how to incorporate it programmatically in your formula. Any ideas? – Shian Han Mar 28 '21 at 14:48
  • 1
    See updated answer and demo spreadsheet. If the answer solve your problem, you can up vote the answer and accept it. – Nabnub Mar 28 '21 at 20:57
  • WOW! You rock :) And thank you and excellent presentation of the solution. Sorry I didn't check back here sooner! – Shian Han Apr 03 '21 at 13:04