Formatting numbers in thousands

  • Thread starter Thread starter John Reimer
  • Start date Start date
J

John Reimer

Is it possible to format numbers in thousands?

For example, I'd want to be able to format 5,123.00 as 5.1

I've been using a formula to divide by 1000, but is there a formatting
string that will accomplish the same thing?

Thanks,
John

--
If you forget about your dreams you die.
Live for them, & they will live for you.

IF you're wondering who the hell wrote this message, or want to know
more :) see my profiles at (e-mail address removed) and (e-mail address removed)
 
John

You can use custom formatting.

Example number in cell: 1234567

With
#.0,
you get 1234.6
(thousands)

with
#.0,,
you get 1.2
(millions)

etc.
one comma for each "group of thousand"

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Go to Format -> Cells, select the Number tab, Custom format, then put:

0.0,;-0.0,

in the dialog box.
 
Example number in cell: 1234567
With
#.0,
you get 1234.6
(thousands)
Click to expand...

Hi Leo

Is there a similar trick for systems who use space as thousand delimiter ?

Best wishes Harald
Excel MVP

Followup to newsgroup only please.
 
Harald Staff said:
Hi Leo

Is there a similar trick for systems who use space as thousand delimiter ?

Best wishes Harald
Excel MVP

Followup to newsgroup only please.
Click to expand...
Hi Harald

Seems to work OK if you enter a space (or two) instead of a comma (or 2).
(Office 2000 SP 3 on Windows XL, SR1 )

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Seems to work OK if you enter a space (or two) instead of a comma (or 2).
(Office 2000 SP 3 on Windows XL, SR1 )
Click to expand...

Can't for the life of me get it to work, not earlier and not now. I'll look into it again
now that I know it can be done. (But today the weather is far too nice for number
formatting. ) Thanks Leo.

Best wishes Harald
 
It worked for me in xl2002 when I changed the "digit gropuing symbol" on the
number tab of the windows regional settings applet.

(But it didn't work when I changed the List separator. Any chance you changed
the wrong one? Or would that just be my lesdyxic problem? <vbg>)
 
Back
Top