Show number in thouands using Formats

[Hari]

HI,

If I have a series of numbers in Cell A1: A10 like 156789 etc

I want to be able to do 2 kinds of operations:-

a) How to set the format, so that number gets displayed as 156.789 or
let' say 157 . I have seen folks using custom formats but not sure how
to set it up in this case.

b) How to set the format in such a way that we may also be see "157
Thousand" for above case. Is it possible using custom formats. (I will
like to do the same thing for 1234567 as "1.2 Million" and so on)

Please guide me.

Regards,
HP
India

[raypayette]

This isn't a formatting problem. The data should be divided by 1000, THEN you deal with the presentation (such as rounding)!

[Leo Heuser]

"Hari" <[email protected]> skrev i en meddelelse
news:[email protected]...
> HI,
>
> If I have a series of numbers in Cell A1: A10 like 156789 etc
>
> I want to be able to do 2 kinds of operations:-
>
> a) How to set the format, so that number gets displayed as 156.789 or
> let' say 157 . I have seen folks using custom formats but not sure how
> to set it up in this case.
>
> b) How to set the format in such a way that we may also be see "157
> Thousand" for above case. Is it possible using custom formats. (I will
> like to do the same thing for 1234567 as "1.2 Million" and so on)
>
> Please guide me.
>
> Regards,
> HP
> India
>

Hi HP

Apparently you have "." as a thousand separator.
To format as 156.789 use the userdefined format #.#

To format as thousands add a point to the right of the format
#.#. or #.#. "Thousand"

to format as millions add 2 points
#.#.. or #.#.. "Million"

#.#... or #.#... "Billion"

etc. Add one point for each "thousand group"

--
Best regards
Leo Heuser

Followup to newsgroup only please.

[RagDyeR]

This *can* be accomplished with custom formatting:

[<=999999]#.#," Thousand";[>999999]#.#,," Million";General

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"raypayette" <[email protected]> wrote
in message news:[email protected]...

This isn't a formatting problem. The data should be divided by 1000,
THEN you deal with the presentation (such as rounding)!


--
raypayette


------------------------------------------------------------------------
raypayette's Profile:
http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=563634

[Hari]

Leo,

Thanx a lot. It works

I just wanted to know as to whether it is also possible to express in
"Hundreds" etc.

Regards,
HP
India

Leo Heuser wrote:
> "Hari" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > HI,
> >
> > If I have a series of numbers in Cell A1: A10 like 156789 etc
> >
> > I want to be able to do 2 kinds of operations:-
> >
> > a) How to set the format, so that number gets displayed as 156.789 or
> > let' say 157 . I have seen folks using custom formats but not sure how
> > to set it up in this case.
> >
> > b) How to set the format in such a way that we may also be see "157
> > Thousand" for above case. Is it possible using custom formats. (I will
> > like to do the same thing for 1234567 as "1.2 Million" and so on)
> >
> > Please guide me.
> >
> > Regards,
> > HP
> > India
> >
>
> Hi HP
>
> Apparently you have "." as a thousand separator.
> To format as 156.789 use the userdefined format #.#
>
> To format as thousands add a point to the right of the format
> #.#. or #.#. "Thousand"
>
> to format as millions add 2 points
> #.#.. or #.#.. "Million"
>
> #.#... or #.#... "Billion"
>
> etc. Add one point for each "thousand group"
>
> --
> Best regards
> Leo Heuser
>
> Followup to newsgroup only please.

[Hari]

RagDyer,

Thanks for conditional number formatting. Its a nice tool to have.

Regards,
HP
India

RagDyeR wrote:
> This *can* be accomplished with custom formatting:
>
> [<=999999]#.#," Thousand";[>999999]#.#,," Million";General
>
> --
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "raypayette" <[email protected]> wrote
> in message news:[email protected]...
>
> This isn't a formatting problem. The data should be divided by 1000,
> THEN you deal with the presentation (such as rounding)!
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile:
> http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=563634

[RagDyeR]

You're very welcome.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Hari" <[email protected]> wrote in message
news:[email protected]...
RagDyer,

Thanks for conditional number formatting. Its a nice tool to have.

Regards,
HP
India

RagDyeR wrote:
> This *can* be accomplished with custom formatting:
>
> [<=999999]#.#," Thousand";[>999999]#.#,," Million";General
>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------------
--
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------------
--
> -------------------
>
> "raypayette" <[email protected]>
wrote
> in message news:[email protected]...
>
> This isn't a formatting problem. The data should be divided by 1000,
> THEN you deal with the presentation (such as rounding)!
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile:
> http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=563634

[Leo Heuser]

"Hari" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Leo,
>
> Thanx a lot. It works
>
> I just wanted to know as to whether it is also possible to express in
> "Hundreds" etc.
>
> Regards,
> HP
> India
>

Hari

You're welcome and thanks for the feedback :-)

Unfortunately its not possible to express in "Hundreds" etc.
with formatting.

Regards
Leo Heuser