specialized formatting
 

I realize I may have initially posted to the wrong place...

[100000000]#\,###\,##\,##\,###;[100000]#\,##\,###;#,###

the above is a custom formatting, which tries to accomplish the following:

99999 or below needs to format as normal US digit grouping (99,999, 65,897,
etc)

100000-999999 needs to format as #,##,###, so 1,00,000 on one end and
9,99,999 on the high end

1000000-999999999 needs to format as #,##,##,###, so 1,00,00,000 on one end
and 9,99,99,999 on the other end

the numbers above 100000000 are then formatted in the same sequence. So if
there is a number 100,000,000,000,000, the last 7 digits are as above, in
2/2/3 grouping, and then the next set of 7 digits is the same 2/2/3 grouping
(1,00,00,000,00,00,000)

The custom format I have gets me part of the way, but not the higher numbers
(you can try and see).

the other thing I cannot do is get it to do negative as well. In other
words, I need negative and positive to both format the same way (with
parentheses as negative indicators, preferably).

If anyone can get me even partly further than I am, greatly useful tool for
me. Thx.


--
Boris

specialized formatting
 

"BorisS" wrote in message ...
I realize I may have initially posted to the wrong place...

[100000000]#\,###\,##\,##\,###;[100000]#\,##\,###;#,###

the above is a custom formatting, which tries to accomplish the following:

99999 or below needs to format as normal US digit grouping (99,999, 65,897,
etc)

100000-999999 needs to format as #,##,###, so 1,00,000 on one end and
9,99,999 on the high end

1000000-999999999 needs to format as #,##,##,###, so 1,00,00,000 on one end
and 9,99,99,999 on the other end

the numbers above 100000000 are then formatted in the same sequence. So if
there is a number 100,000,000,000,000, the last 7 digits are as above, in
2/2/3 grouping, and then the next set of 7 digits is the same 2/2/3 grouping
(1,00,00,000,00,00,000)

The custom format I have gets me part of the way, but not the higher numbers
(you can try and see).

the other thing I cannot do is get it to do negative as well. In other
words, I need negative and positive to both format the same way (with
parentheses as negative indicators, preferably).

If anyone can get me even partly further than I am, greatly useful tool for
me. Thx.


--
Boris

Hi Boris.
You have room for one more format, without condition,
where none of the other two conditions are true.
But that's it, I think.
How about a formula?
=SUBSTITUTE(IF(A1<0,"("&TRIM(TEXT(-A1,"# ## ## ### ## ## ##0"))&")",TRIM(TEXT(A1,"# ## ## ### ## ## ##0")))," ",",")
Not at all smart like the custom format, but there is more elbow room with formulas.
Hans T.

specialized formatting
 

Hans, thanks for that. Good idea, but I specifically need to do this across
lots of places, and need to distribute to others. Having them put in
formulas in an additional column/row is just not an option, practically
speaking. So was hoping for custom formatting. Sounds like I'm at a dead end.
--
Boris


"Hans Terkelsen" wrote:


"BorisS" wrote in message ...
I realize I may have initially posted to the wrong place...

[100000000]#\,###\,##\,##\,###;[100000]#\,##\,###;#,###

the above is a custom formatting, which tries to accomplish the following:

99999 or below needs to format as normal US digit grouping (99,999, 65,897,
etc)

100000-999999 needs to format as #,##,###, so 1,00,000 on one end and
9,99,999 on the high end

1000000-999999999 needs to format as #,##,##,###, so 1,00,00,000 on one end
and 9,99,99,999 on the other end

the numbers above 100000000 are then formatted in the same sequence. So if
there is a number 100,000,000,000,000, the last 7 digits are as above, in
2/2/3 grouping, and then the next set of 7 digits is the same 2/2/3 grouping
(1,00,00,000,00,00,000)

The custom format I have gets me part of the way, but not the higher numbers
(you can try and see).

the other thing I cannot do is get it to do negative as well. In other
words, I need negative and positive to both format the same way (with
parentheses as negative indicators, preferably).

If anyone can get me even partly further than I am, greatly useful tool for
me. Thx.


--
Boris

Hi Boris.
You have room for one more format, without condition,
where none of the other two conditions are true.
But that's it, I think.
How about a formula?
=SUBSTITUTE(IF(A1<0,"("&TRIM(TEXT(-A1,"# ## ## ### ## ## ##0"))&")",TRIM(TEXT(A1,"# ## ## ### ## ## ##0")))," ",",")
Not at all smart like the custom format, but there is more elbow room with formulas.
Hans T.