Jun 30 2021 03:11 AM
I would like to create a custom number format for engineering applications that use suffixes:
Example:
0.024 => 24 m
2.55E-5 => 25.5 µ
3.7 => 3.7
3700 => 3.7 k
I already use the custom format ##0.00\ E-#
But this format is not fully useful for several reasons:
1: for numbers in ]-1000,-1] U [1,1000[, we get a tailing E0 that I would like to remove:
e.g: 34.7 => 34.70 E0 and should stay 34.7
2: I would like to have the possibility to add a suffix like A for Ampere to have finally "34.87 nA"
which is really more readable than "34.87 E-9 A"
Of course, I can write my own VBA function to do it (I have done it already), but in this case I have to use 2 cells, one that contains the value and one that contains the text... the target Cells will contain text instead of numbers. I would really use a number format that changes only the appearance in the cell, not the type of its content. So, if the appearance of cell "A1" is "34 mA", it's value is still 0.034 and
if B1 cell contains "=100*A1", the value of B1 must be 3.4
So my questions are:
1. In the notation ##0.00E-#, is it possible to remove easily the tailing E0
2. Is it possible to write my own custom format to use engineering suffixes (even as a dll) or excel addon ?
Thank you
Jul 05 2021 05:49 AM
Jul 06 2021 02:19 AM
Jul 06 2021 05:04 AM