Exponential format... without the exponent

[TomH]

I've been asked by a coworker if it is possible to display entries in a cell as if they were divided by 1,000. He does an extraction from his legacy database and pastes the results into excel. He *could* paste the data in another section and write formulae that divide by 1000 in his display area... (easy enough) but I was wondering if there is a format that might give him what he wants.

If I use exponential formatting, I can get "4792" to display as "4.8E+03". But, my question is whether I can get Excel to just display the "4.8" and drop off the "E+03". I tried everything I could think of in the format dialog but couldn't make it work. Visual Basic, perhaps?

Any help is greatly appreciated!

 

[stopher]

try a custom format of:
#.#,

 

[TomH]

try a custom format of: #.#,

Incredible. That worked. I tried all kinds of different custom formats and couldn't get it to work. I obviously have some reading to do.

THANKS!!

Just a heads up for anyone using this: Any values rounding to a whole number will be shown with the decimal but no trailing zero (e.g. 2992 will show "3."). Reduce the number of decimal places until the decimal disappears and then restore it to one place. The zero will then show. Odd little quirk.