Custom Engineering number formatting

[Arnaud_Bertrand]

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

[#2]

It is a little known fact that a custom number format can contain conditions. The custom format below has three possible formats (separated by a semi-colon): numbers less than -1000, numbers greater than 1000 and the rest:
[<-1000]-##0.00 E-#;[>1000]##0.00 E-#;#,##0.0
If you want to add prefixes, you can consider using a set of conditional formats. Still, you will have to add the unit character manually for each cell with a different unit of measure.
I hope one day Excel will become aware of units of measure, so it might disallow you te try to subtract Amperes from kilometers.

[#3]

Thank you for your reply. Unfortunately, Excel has a big limitation with conditions (only 2 conditions are allowed) : as mentioned here:
https://stackoverflow.com/questions/25570353/excel-custom-number-format-with-three-conditions
It seems that Excel (and related spreadsheet software) has a fundamental limitation that it can only use two conditionals in custom number formats. Apparently, internally it is using 4 possible display categories: number condition 1, number condition 2, other number, and text. Setting custom number formats can only redefine the two number conditions. You cannot directly control the "other" condition.

And with your proposal, it will work for number having |x| >1 but in the case of 4E-3 it will fail and just write 0.0
The problem is that 2 conditions are really not sufficient to do the job except if there is a way to use a function as ABS() in the condition but I don't think it is possible.

[#4]

Hence my suggestion to expand on this using conditional formatting rules.