number format of ##0.0E+0 but don't display the E in cell.

[Leon V (AW)]

I have some numbers in Killograms which need to remain in Killograms for other formalas that use these values however I would like excel to display the values in grams (which normally would be .01Kg = 10.0E-3 grams) but without displaying the E value at the end. so 0.1 would be displayed as 100.

anyone know how to do that in a number format (if it's possible)?

I can display 3 grams in Kg using format {0.###,} and the value still counts as 3 for equations, I just can't figure out how to do it the other way around.

[Jakobshavn]

Select the cells you wish to format and run this:

Please Login or Register  to view this content.

[Leon V (AW)]

that sort of works however the only way to keep the values up to date is to have it run on the range as part of a sheetchange sub and I need the workbook to remain macro free.
I can display the correct number with the format 0,%%% but it shows the damn % characters at the end.

[MrShorty]

Available number format codes: http://office.microsoft.com/en-us/ex...372.aspx?CTT=1 The comma can be used to scale numbers by 10^3 -- as you noted, it easily formats 3000 (g) as 3 (kg) (format code 0,). I have often wished for a code that will do the opposite (display a value of 3 (kg) as 3000 (g)), but I do not think Excel will do it. The closest I have found is to use some kind of scientific/engineering format -- and even that is not a perfect solution.

In short, exactly what you want cannot be acheived (to my knowledge) by number formatting alone. The imperfect number formats you have already suggested are the only ways I know of to do what you want. The only other suggestion I can make is to store the "kg" number somewhere out of the way (hidden maybe?) and use that cell in calculations. Where you want the kg number displayed as g, you can then have a formula =cellref*1000.

[lecxe]

that sort of works however the only way to keep the values up to date is to have it run on the range as part of a sheetchange sub and I need the workbook to remain macro free.
I can display the correct number with the format 0,%%% but it shows the damn % characters at the end.

Hi

Well, if you don't want to see the % characters, one option is to send then to a second line that you don't display. Not a perfect solution, but it works.

If you want to try it, enter in the custom format box

0,%%%

place the cursor between the comma and the first % and press CTRL-J. Then press OK.

Set the cell format to Wrap Text. Keep the row height to display just 1 line. This gets you the multiply by 1000 that you want.

As I said, not a perfect solution.

[Leon V (AW)]

Is it possible to fix a custom font (character set) to a workbook? maybe I could creat a font with a blank % character.

[lecxe]

Hi

I've done this using a modified font before and it works fine but, unfortunately, there's no easy way to embed a font in Excel. This means you'd have to install the font in windows which may not be practical if you want to share the spreadsheet.
As you may know this would not be a problem in Word, where you can save a document with the fonts embedded.

Remark: I'm using Office 2010. I guess that this may not be a problem in some future version.