• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Using Excel

I want to display a number such as 0.0000004 as 400 in the cell, but maintain the value of the number.
0
nikitav8
Asked:
nikitav8
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
You can do this but it's tricky. Use a custom number format. Enter the format as follows:

0,<CTRL+J>%%%%%%

Actually press CTRL+J versus entering the text "<CTRL+J>".

Format the cell with word wrap.

Kevin
0
 
dsackerContract ERP Admin/ConsultantCommented:
You can format the cell with the custom value of:  #,<Ctrl-J>%%%%%%

Zorvek has it right, and sooner than mine, so please award him all the pointer.
0
 
arildj78Commented:
WHY does this work? It seems like the most random command ever! What's the effect of Ctrl-J and what's up with WordWrap?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Each comma divides the number by 1,000. Each percent sign multiplies the number by 100. The commas are hidden but the percent signs are displayed. By entering CTRL+J a line feed is placed in the formatted string. Setting the cell to word wrap puts the percent signs on a second line that is not displayed unless the row is expanded.

Kevin
0
 
arildj78Commented:
That is a sweet solution to something MS haven't bothered offering us :)
However, to be able to replicate your result with a Norwegian locale I had to replace the comma with a space.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now