Using HTML5 Fractions in Exported Excel Tables

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

Name Hex Dec Result
½ U+000BD 189 ½
⅓ U+02153 8531
¼ U+000BC 188 ¼
⅕ U+02155 8533
⅙ U+02159 8537
⅛ U+0215B 8539
⅔ U+02154 8532
⅖ U+02156 8534
¾ U+000BE 190 ¾
⅗ U+02157 8535
⅜ U+0215C 8540
⅘ U+02158 8536
⅚ U+0215A 8538
⅝ U+0215D 8541
⅞ U+0215E 8542

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

Result Named
Code
Hex
Code
Dec
Code
½ ½ ½ ½
⅓ ⅓ ⅓
¼ ¼ ¼ ¼
⅕ ⅕ ⅕
⅙ ⅙ ⅙
⅛ ⅛ ⅛
⅔ ⅔ ⅔
⅖ ⅖ ⅖
¾ ¾ ¾ ¾
⅗ ⅗ ⅗
⅜ ⅜ ⅜
⅘ ⅘ ⅘
⅚ ⅚ ⅚
⅝ ⅝ ⅝
⅞ ⅞ ⅞

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

D E F G H I J K L
1 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/80
2 2/1 2/2 2/3 2/4 2/5 2/6 2/7 2/8 2/8.
3 3/1 3/2 3/3 3/4 3/5 3/6 3/7 3/8 3/8A
4 4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 6 4/8
5 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 7 5/8
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 6/8 8 6/8
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 7/8 9 7/8
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8″ pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

Function MakeFracs(Arg As String) As String
   Dim sIN     As String
   Dim sOUT    As String
   Dim i As Long, j As Long
   Dim n As Long, d As Long
   Dim Fracs(1 To 15, 1 To 3) As String
   
   Fracs(1, 1) = "½": Fracs(1, 2) = "½": Fracs(1, 3) = "½"
   Fracs(2, 1) = "⅓": Fracs(2, 2) = "⅓": Fracs(2, 3) = "⅓"
   Fracs(3, 1) = "¼": Fracs(3, 2) = "¼": Fracs(3, 3) = "¼"
   Fracs(4, 1) = "⅕": Fracs(4, 2) = "⅕": Fracs(4, 3) = "⅕"
   Fracs(5, 1) = "⅙": Fracs(5, 2) = "⅙": Fracs(5, 3) = "⅙"
   Fracs(6, 1) = "⅛": Fracs(6, 2) = "⅛": Fracs(6, 3) = "⅛"
   Fracs(7, 1) = "⅔": Fracs(7, 2) = "⅔": Fracs(7, 3) = "⅔"
   Fracs(8, 1) = "⅖": Fracs(8, 2) = "⅖": Fracs(8, 3) = "⅖"
   Fracs(9, 1) = "¾": Fracs(9, 2) = "¾": Fracs(9, 3) = "¾"
   Fracs(10, 1) = "⅗": Fracs(10, 2) = "⅗": Fracs(10, 3) = "⅗"
   Fracs(11, 1) = "⅜": Fracs(11, 2) = "⅜": Fracs(11, 3) = "⅜"
   Fracs(12, 1) = "⅘": Fracs(12, 2) = "⅘": Fracs(12, 3) = "⅘"
   Fracs(13, 1) = "⅚": Fracs(13, 2) = "⅚": Fracs(13, 3) = "⅚"
   Fracs(14, 1) = "⅝": Fracs(14, 2) = "⅝": Fracs(14, 3) = "⅝"
   Fracs(15, 1) = "⅞": Fracs(15, 2) = "⅞": Fracs(15, 3) = "⅞"

   i = VBA.InStr(1, Arg, "/", vbTextCompare)
   If i = 0 Then   'there's no fraction
      MakeFracs = Arg
   ElseIf Mid$(Arg, i, 3) Like "/##" Then   'not HTML5
      MakeFracs = Arg
   ElseIf Mid$(Arg, i - 2, 4) Like " [1-7]/[234568]" Then
     sOUT = Mid$(Arg, i - 1, 3)
     n = VBA.Val(Left$(sOUT, 1))   'numerator
      d = VBA.Val(Right$(sOUT, 1))   'denominator
      If n < d Then
         If d Mod n = 0 Then
            d = d / n
            n = 1
         ElseIf d Mod 2 = 0 And n Mod 2 = 0 Then
            d = d / 2
            n = n / 2
         End If
         sIN = "&frac" & n & d & ";"
         For j = 1 To 15
            If Fracs(j, 1) = sIN Then
              sIN = Fracs(j, 2) '<-or Fracs(j, 3) for HEX
               Exit For
            End If
         Next j
         MakeFracs = VBA.Replace(Arg, sOUT, sIN)
      Else
         MakeFracs = Arg
      End If
   Else
      MakeFracs = Arg
   End If

End Function

 
At least that’s all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added “j-loop” in the middle translates the &fracnd;’s into Dec code. This works fine, though it’s a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

N O P Q R S T U V
1 1/1 &#189; &#8531; &#188; &#8533; &#8537; 1/7 &#8539; 1/80
2 2/1 2/2 &#8532; &#189; &#8534; &#8531; 2/7 &#188; &#188;.
3 3/1 3/2 3/3 &#190; &#8535; &#189; 3/7 &#8540; &#8540;A
4 4/1 4/2 4/3 4/4 &#8536; &#8532; 4/7 &#189; 6 &#189;
5 5/1 5/2 5/3 5/4 5/5 &#8538; 5/7 &#8541; 7 &#8541;
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 &#190; 8 &#190;
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 &#8542; 9 &#8542;
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

1/1 ½ ¼ 1/7 1/80
2/1 2/2 ½ 2/7 ¼ ¼.
3/1 3/2 3/3 ¾ ½ 3/7 ⅜A
4/1 4/2 4/3 4/4 4/7 ½ 6 ½
5/1 5/2 5/3 5/4 5/5 5/7 7 ⅝
6/1 6/2 6/3 6/4 6/5 6/6 6/7 ¾ 8 ¾
7/1 7/2 7/3 7/4 7/5 7/6 7/7 9 ⅞
8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

 
Frankly, I’m not sure that this is an improvement. You’ll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn’t, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it’s being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬

One Comment

  1. […] invoked from a form instead of daisy-chained message boxes. I got lazy. The MakeFracs() function is here. If you want to put something in the upper-left cell, and still get row headers, put it in after […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: