Excel 2019 How to set Cell format in Scientific notation to thousands only, E9, E6, -E6, -E12

Question

I'm doing calculations and I would like to keep all the data in scientific notation to the powers of 3 like I do when I'm doing real life calculations, currently I'm getting 7.025E7 instead of 70.25E6.

Thanks in advance

Formating individual cells, changing the amount of decimals.

I can only make it work by splitting on "E" and adjusting mantissa and exponent separately — Tom Sharpe, May 03 '23 at 07:41

Puha Technologies · Accepted Answer · 2023-05-08T00:36:43.010

3

Thank you guys, I chose a modification of chris neilsen's answer. I ended up using a custom format of ###.###E+0

Screen shots as requested by @Tom Sharpe

Custom format I entered into Excel 2019

End result of data in Excel 2019 Some of the cells in screen shot 2 do not have the custom format (cells displaying 0, 1, 1000) also row 10 displaying 825.043

edited May 08 '23 at 00:36

answered May 04 '23 at 00:37

Puha Technologies

41
7

Interesting - I have learnt something! Perhaps show a screenshot of it working (it obviously does) and I will upvote. – Tom Sharpe May 04 '23 at 17:31

score 0 · Answer 2 · answered May 03 '23 at 10:40

For what it's worth, I think you would have to do this, but of course it requires an extra column rather than just a change of format:

=LET(split,TEXTSPLIT(TEXT(A1,"0.000E+00"),"E"),mantissa,INDEX(split,1),exponent,INDEX(split,2),
mod,MOD(exponent,3),mantissa*10^mod&"E"&exponent-mod)

Excel 2019 How to set Cell format in Scientific notation to thousands only, E9, E6, -E6, -E12

2 Answers2