Excel 2007 dropping a comma (separator)

  ttphilip 19:29 07 Jan 08
Locked

I am a new user to excel 2007, upgrading from Excel 2003. When I format a large number to insert a comma for the thousands it does not work as I would expect. i.e if it is a 4 digit number it does ok ....e.g. 1234 becomes 1,234.
However if it is a larger 7 digit number it does not put in the second comma i.e. 1234567 becomes 1234,567. How can I make it place a comma between the "1" and the "2" as any other program would do.
FYI I am doing this by: format cells, number, and checking the box for 'use 1000 separator'

  Fermat's Theorem 23:46 07 Jan 08

If you have 1234567 in a cell, you can just click on the comma in the Number tab on the Home Ribbon in Excel 2007. This will then give you 1,234,567 as you wish to have it.

Hope this helps :-)

  ttphilip 12:00 08 Jan 08

Sorry, I should have mentioned that I have tried that and it gives the same outcome 1234,567 (and it adds .00 at the end of the nummber which i do not want)

  Fermat's Theorem 13:30 08 Jan 08

Very strange - it works perfectly on my computer! You can eliminate the .00 at the end of the number by clicking the "decrease decimal" button on the same Number tab on he Home ribbon :-)

  ttphilip 15:24 08 Jan 08

what version of excel 2007 do you have - mine is 12.0.6024.5000 mso (12.0.6017.5000)
also, I am running on Vista

  Fermat's Theorem 15:47 08 Jan 08

version that you have. Please do not take offence if I ask you to try a very simple task. In Cell A1 please type in 1234567. Then click any other cell. Now go back to Cell A1 and click on it. While Cell A1 is now alive click on the comma button in the Numbers tab on the Home Ribbon. You should now have 1,234,567.00 in Cell A1. As in my previous post, click on the decrease decimal button on the same Numbers tab - click it twice to get rid of the two decimal places. It should work for you!

:-)

  VoG II 17:12 08 Jan 08

Fermat's Theorem's method works for me. Try a Custom format

_-* #,##0_-;-* #,##0_-;_-* "-"??_-;_-@_-

  ttphilip 17:18 08 Jan 08

I followed your instructions to the letter. Everything worked as you said except that when I click on the comma on the home ribbon, i got 1234,567.00 i.e. still no comma between 1 and 2.

this is really strange now that you tell me you have same version and it works ok for you.

I have just checked with one of my work collegues and his is doing the exact same thing as mine !! ??

  VoG II 17:27 08 Jan 08

Control Panel > Regional and Language Options. Check the number format. Mine is set at

123,456,789.00

  Fermat's Theorem 17:31 08 Jan 08

as has been confirmed by VoG.

My guess is that you have already pre-formatted the cell to give the 1234,567 format. Try my previous instruction on a new blank worksheet and see what happens.

Also, wondering did you do it on your colleague's machine, or was it your colleague who did it?

I cannot replicate your error, so I can't be of any more help to you. Sorry. :-(

Try the Custom format as suggested by VoG.

  ttphilip 17:41 08 Jan 08

VOG's suggestion fixed the problem !!!! Thank You !! I am in Trinidad - my language setting was English (Trinidad). I changed it to English (USA) and everything works as it should !!!! :):)

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced