Formatting SI units in Excel

"WAYNEL" schrieb im Newsbeitrag news: snipped-for-privacy@m58g2000cwm.googlegroups.com...

Hello WayneL,

are you talking about SI-units like m, kg, s or do you think on the SI-prefixes p, n, u, m, k, ... which are numeric multipliers.

Best regards, Helmut

Several examples would help.

Wouldn't it be nice if Excel could display '1n', instead of '1.00E-9' or worse '0.000000001'? And it would be great if I could type 4M7 and have it interpreted as 4.7 million.

I'm surprised this is so rarely done. It not hard or anything. I only know of gnuplot and spice that use some of this, and spice even gets it wrong, confusing M(ega) and m(illi). Pathetic.

Jeroen Belleman

IMO it would be reasonably easy to do in VBA.

Does excel support "custom" formats? You could write a little parser in VBA, but I don't know of anything OTS.

Good Luck Rich

if your number is in cell A1 use this formula to fudge it:

=concatenate(A1*1000^-int(log(A1)/3),mid("afpnum kMGTE",int(log(A1)/3)+7,1))

If you'd prefer (non-SI) "infix multiplier" notation, try this one. (watch out for wrap)

=replace(concatenate(A1*1000^-int(log(A1)/3)," "),mod(int(log(A1)),3)+2,1 ,mid("afpnumRKMGTE",int(log(A1)/3)+7,1))

Either way this is for looks only, the results will not have a valid numerical value and you can't enter numbrers in this format

If it's so easy add it to gnumeric, and post mail the maintainers a patch :)

Bye. Jasen

Newsbeitragnews: snipped-for-privacy@m58g2000cwm.googlegroups.com...

multipliers.http://www.chemie.fu-berlin.de/chemistry/general/si_en.html

Hi Helmut

Yes.

Regards

WayneL

Newsbeitragnews: snipped-for-privacy@m58g2000cwm.googlegroups.com...

multipliers.http://www.chemie.fu-berlin.de/chemistry/general/si_en.html

All the PSpice utilities my oldest son wrote for me can accept entries like "8.46p" etc.

I believe it can be done in Excel as well, but I couldn't find it.

Mark/qrk, Didn't you show me how to do that once? Or was it someone else?

...Jim Thompson

"Jim Thompson" wrote in message news: snipped-for-privacy@4ax.com...

When you enter the field to update it you need to erase the contents. Then when you leave the field you parse the contents and rewrite as needed.

Errrrm. "Yes" to which part of the question?