Converting fractions to decimal values

Recently, I’ve been getting Excel spreadsheets from clients that contain measurements in inches and/or fractions of an inch. In some cases, the cells with these measurements actually contain numbers but are displayed as fractions using one of the many formatting options Excel has.

Excel cell formatting options

In these cases, it’s easy for me to change the formatting to a standard decimal representation before exporting the sheet as a CSV file.

Often, though, the measurements are entered as text. This is done by prefixing the data with a single quote mark, e.g.,

'2 5/16

or maybe

'2-5/16

These need to be converted into a decimal representation before exporting to CSV, so Pandas will recognize them as numbers rather than strings.1

To handle this situation, I created a Keyboard Maestro called Floatize. To use Floatize, I

Here’s the macro:

Floatize Keyboard Maestro macro

It takes the clipboard, sends it to a script as standard input, and then puts the output back into the clipboard. Here’s the Python script it runs:

python:
 1:  #!/usr/bin/env python
 2:  
 3:  from fractions import Fraction
 4:  import re
 5:  import sys
 6:  
 7:  def floatize(s):  
 8:    if s in ['', 'n/a', 'none']:
 9:      return ''
10:    else:
11:      try:
12:        num = float(s)
13:      except ValueError:
14:        t = re.sub(r'(.*\d) *- *(\d.*)', r'\1 \2', s)
15:        if t[0] == '-':
16:          num = -float(sum(Fraction(x) for x in t[1:].split(None, 1)))
17:        else:
18:          num = float(sum(Fraction(x) for x in t.split(None, 1)))
19:      return "{:f}".format(num)
20:    
21:  for line in sys.stdin.read().splitlines():
22:    item = line.strip().lower()
23:    print floatize(item)

The bulk of the fraction parsing is done by the fractions module in either Line 16 or Line 18. The rest of the script is an attempt to manipulate the oddball types of data I get into a format that fractions understands. Here’s what it can handle:

The macro works perfectly in Numbers and BBEdit, but not in Excel. For some reason, nothing happens to the clipboard if I run the Floatize macro while Excel is the active application. I can, however, copy the column of fractions, activate another app, run Floatize, switch back to Excel and paste the converted clipboard. Another example of Excel not acting like a proper Mac app and another reason I move data out Excel as quickly as I can.

Despite its inability to work inside Excel, this macro has been very handy over the past couple of weeks. I’ve used it to clean up several data sets that were loaded with fractions.

By the way, I will block anyone who tweets that I wouldn’t have this problem if I used the metric system.

Update Nov 13, 2017 10:04 PM  The Twitter consensus is my problem in using this macro in Excel stems from Excel’s clipboard dumbassery. It apparently uses a private clipboard instead of the system clipboard until you bring another application to the front. Hence the macro’s inability to convert the clipboard unless I switched away from Excel.

Jimmy Hartington suggested this workaround:

@drdrang Excel does something weird with the clipboard as you noticed.
Try this. Copy in Excel. Press Escape. Run the macro.
By pressing Escape I think Excel stops messing with the clipboard.
Jimmy Hartington (@jimmyhartington) Nov 12 2017 11:06 PM

This works perfectly and pointed me in the direction to make a better version of the macro, one that does all the copying, converting, and pasting:

New Floatize Keyboard Maestro macro

Note the conditional action that simulates the pressing of the Escape key only if Excel is the front application. This is necessary because Escape causes Numbers to deselect the column, which screws up the paste action at the end of the macro.

With this macro I can now select the column that needs converting and just press ⌃⌥⌘F to do the conversion in a single step. Thanks, Jimmy!


  1. There are probably ways to get Pandas to do the conversion as it imports the data, but I feel more comfortable—more in control—if I do it ahead of time. ↩︎