Hi David,

Sorry, I wasn't being specific. When I was referring to the exponent, I
meant
the letter 'E' and the number. For example: E+9 or E-6

In engineering notation, the exponent is denoted by a letter and has the
following mapping:

123E-15 - 123 f
123E-12 - 123 p
123E-9 - 123 n
123E-6 - 123 u
123E-3 - 123 m
123E0 - 123
123E3 - 123 k
123E6 - 123 M
123E9 - 123 G
123E12 - 123 T
and so on...

I need to analyse data that spans across large orders of magnitude, as a
result it is much more intuitive for me to read numbers in engineering
notation.

Regards,
Joshua.

"David McRitchie" wrote:

Hi Joshua,
This will get you the characters you ask for but if involves a helper column,
and the result is text, which maybe is what you need for your other program.

=SUBSTITUTE(TEXT(A16,"##0.0E+0"),"E","p")

or if you want E- but not E+ use
=SUBSTITUTE(TEXT(A16,"##0.0E-0"),"E","p")

Why the other letters?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joshua Wong" wrote
Thank you for the pointer, it was very helpful. The format string
displays numbers in engineering notation in that the exponent part is always
in the power of 3. But I was hoping that there is a way to replace the
exponent part with suffixes such as p, n, u, m, k, M, G, T ...etc

So is it trivial to do this without writing VB code as shown on your