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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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