KiwiSteve,
Thanks for the tip. I found a similar tip to use TEXT on
this page.
I have since figured out a solution (albeit a very complicated one). The new formula is now posted on my site, but I've included it here also:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
=TEXT(TEXT(value,"."&REPT("0",sigfigs)&"E+000"),
"0"&REPT(".",(sigfigs-(1+INT(LOG10(ABS(value)))))>0)&
REPT("0",(sigfigs-(1+INT(LOG10(ABS(value)))))*
((sigfigs-(1+INT(LOG10(ABS(value)))))>0)))
I just finished creating a corresponding VBA function, which I will post on my site soon. There was a problem in the function that was driving me crazy when the value was equal to 10, 100, 1000, etc.
It turns out that in most cases, you don't really need to use VALUE(cell), because Excel's smart enough to figure out that the string is numeric. However, I don't know how far I'd trust that.
Enjoy,
Jon