Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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