**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:

- 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.