Scientific notation - Fixed exponent, fixed significant digits

So how would the number 12 appear?

or 0.0000001?

There's no way to tell how many significant digits there are in 120000E-3 -- it's somewhere between 2 and 6.

The only way to show three significant digits is to format as 0.00E+0 and let the exponent vary.

EDIT: Also, 0.89E-3 only has two significant digits.

I think this is as close as you'll get, and the result is text, and the value is rounded to the significant digits:

	A	B	C
2	0.000 012 345	0.012E-3	B2: =TEXT(TEXT(1000*A2, "0.00E+0"), LOOKUP(--TEXT(1000*A2, "0.00E+0")/1000, {0,10,100,1000}/10000, {"0.000","0.00","0.0","0"})) & "E-3"
3	0.000 123 450	0.123E-3
4	0.001 234 500	1.23E-3
5	0.012 345 000	12.3E-3
6	0.123 450 000	123E-3
7	1.234 500 000	1230E-3
8	12.345 000 000	12300E-3
9	123.450 000 000	123000E-3

If it helps, I have been wishing for something like this for a long time (this is from 2005: https://www.excelforum.com/excel-gen...ng-format.html ). Something text based like shg has proposed is as close as I have got, and I generally don't like storing numbers as text.

Good point. I'll settle for fixed exponent then:
##0.00E+0

Oh, btw, is it possible to use the small "e"?
Not terribly relevant, but I like it better.

is it possible to use the small "e"?

Wrap it in LOWER()