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
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.
Twenty-two years ago, I wrote a thesis. It wasn’t a very good thesis, but it did what it needed to do. For years, its model files have been unreadable, because the spreadsheets were written in a ~1992 version of Microsoft Works. These are old files:
1993-04-21 03:19 newmodel.wks
1993-04-21 03:19 newmodel.wk1
1993-04-08 06:29 pr_fa.wks
Quite recently, LibreOffice realized that there are old files out there that (unlike my thesis models) could still be useful. As they have no commercial requirement to only support the latest and greatest, LibreOffice added the ability to read these ancient works. So my old stuff lives again:
I found a screen dump that I used back in ’93 to illustrate the layout. The display was colour, but here it is brought back to life with a little bit of antialiasing:
LibreOffice can also read old AppleWorks files. Although Works 6 still runs on Catherine‘s Mac, it looks a bit … dated:
Thanks, LibreOffice! It’s sometimes easy to forget (like right after updated to Ubuntu 15.04, which decided that BlueTooth support was kinda optional unless you jumped through hoops …) that people do write software just to be more useful.
The magic custom format string for this is:
Works with LibreOffice and Excel on every platform I’ve tried.
I use an annoying program that labels its output A..Z, AA..AZ, BA … rather than numerically from 1. This is annoying, as a spreadsheet won’t sort it correctly (it does A, AA, AB …). The following code will convert this code to the right numbers, assuming your alpha code is in cell B3:
This will only work for codes of two characters or less, and is case sensitive.