Using SI prefixes/multipliers in spreadsheets

Note: I’ve lightly tested this with Microsoft Excel (Windows 10), Excel Online, Google Sheets and LibreOffice Calc. It seems to work. Like all spreadsheet data conversions, please verify before trusting your PhD thesis tables to it …

Asked on the GTALUG mailing list the other week:

Does anybody know how to display and work with SI numbers like 10k or 
20M or 40G within LibreOffice?

I came up with the following formula, in this example for data in cell D3:

=IF(LEN(T(D3))=0, D3, CONVERT(VALUE(LEFT(D3, LEN(D3) - 1)),
    RIGHT(D3, 1) & "m", "m"))

which results in:

Input Value
1u 1.00E-06
10u 10.00E-06
100u 100.00E-06
1m 1.00E-03
10m 10.00E-03
100m 100.00E-03
1 1.00E+00
10 10.00E+00
100 100.00E+00
1k 1.00E+03
10k 10.00E+03
100k 100.00E+03
1M 1.00E+06

The right column is displayed in LibreOffice Calc’s newly(ish)-supported engineering notation.

This function works through creative (mis-)use of the CONVERT() function:

  1. if the argument is a numeric value, pass it through;
  2. if the argument is a string, return CONVERT(«numeric part», "«prefix»m", "m"). This is lightly misusing overloading the unit conversion function by going via metres, but it saves having a lookup table.

This function doesn’t work with IEC 60027-2 binary prefixes, but they’re silly and I wouldn’t be caught dead using ’em.