Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want make a Number Custom Format Pattern in Microsoft Excel 2010.

It looks like multiply with 1000.

Please read following example below:

1 would turn 1,000

12.3 would turn 12,300

123456.789 would turn 123,456,789

Thanks for reading and appreciating for answering.

share|improve this question
3  
Perhaps I'm overlooking something, but why isn't 123456789 becoming 123456789,000? Are you replacing all (implicit) decimal points with a comma and ensuring 3 decimal places, or using the comma as a digit grouping symbol? –  Karan Feb 28 '13 at 1:33
    
123456789 become 123,456,789 due to its thousand separation original in Excel. 12.3 turn 12.300 because it multiply with 1000. –  Peter PAD Feb 28 '13 at 1:37
    
Ok, so what's common between the first two examples and the third? Where is the "multiply with 1000" in the third example? Shouldn't it be 123,456,789,000 instead? –  Karan Feb 28 '13 at 1:38
    
dot "." will turn "," instead. The same as when we multiply it with 1000. –  Peter PAD Feb 28 '13 at 1:40
1  
Wow, let me spell it out for you. What is the rule common to all the examples? 1 x 1000 = 1000 = 1,000 after grouping. 12.3 x 1000 = 12300 = 12,300 after grouping. Similarly 123456789 x 1000 = 123456789000 = 123,456,789,000 after grouping? Why is 123456789 simply being grouped without being multiplied by 1000 too? –  Karan Feb 28 '13 at 1:44

2 Answers 2

Since you insist on doing this using a Custom Number Format, here's a nifty trick you can use:

  1. Enter the numbers and use the following as the custom number format:

    #,###,%%%
    
  2. This is what you'll see:

    1

    So close, right?!

  3. This is where the trickery comes in. There's no easy way to hide the percent symbols, but here's what you can do. Open the Format Cells dialog again and position your cursor between the second comma (reinsert if it has disappeared) and the first percent symbol, like so:

    2

  4. Now press Ctrl+J and you'll see this:

    3

    As you can see, the three percent symbols have magically* disappeared! However, when you ok your way out the cell contents will still look exactly the same as before, so what did this achieve? Patience, my young Padawan... :)

  5. Select the column with the numbers, open up the Format Cells dialog once more, go to the Alignment tab this time and select the Wrap text checkbox under Text control:

    4

  6. Ok your way out and boom!

    5

* All right, you got me, it's not magic. But how does it work? Apparently, Ctrl+J inserts a Carriage Return into the custom number format text field (how non-obvious is that?!). You can confirm this by using the down arrow key after that sequence, and you might just be able to make out that there's another line of text there with the three percent symbols.

Now you can probably guess the rest. Enabling text wrap just wraps the text in the cells so the percent symbols drop to the next line. But if you don't resize the cells to be bigger in height, they still look like they have only a single line of text in them. Sneaky, no? :) Here's what it looks like if you increase the cell height:

6

Note that the actual values in the cells are still 1, 12.3 and 123456.789 respectively, only the way they're displayed has been affected.

share|improve this answer
    
Very nice, Karan! –  teylyn Mar 3 '13 at 1:46
    
Thanks, coming from an Excel MVP that's high praise indeed! :) –  Karan Mar 3 '13 at 1:48

If you use a formula, why not just multiply by 1000 instead of a construct with LEN() and TEXT().

By the way, a number format can be used to suppress zeros in numbers greater than 1000, but it cannot be used to add magnitudes. Therefore, a formula will be required.

=A1*1000

... and then format to your liking, with thousand separators and/or decimal points.

share|improve this answer
    
I want to use Number Custom Format only –  Peter PAD Feb 28 '13 at 7:21
    
Again, you can NOT use a number format to add zeros to a number in the way you describe. Excel just does not work this way. The only way to show 123456.789 as 123,456,789 is with formulas. The Len/Text combo above will render text, which will be hard to use in follow-on calculations, the multiplication by 1000 will return a number, which will be easy to use in follow-on multiplication, either as the original value (just divide by 1000 again) or as the magnified value. –  teylyn Feb 28 '13 at 8:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.