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:
The right column is displayed in LibreOffice Calc’s newly(ish)-supported engineering notation.
This function works through creative (mis-)use of the
- if the argument is a numeric value, pass it through;
- if the argument is a string, return
CONVERT(«numeric part», "«prefix»m", "m"). This is lightly
misusingoverloading 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.