Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 191
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 191
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Specialized custom number format BrianW New Users to Excel 5 November 27th 08 02:19 AM
expanding custom formatting without removing existing cell formatting? Keith Excel Worksheet Functions 3 December 27th 06 02:54 PM
Creating a specialized worksheet for my credit cards. johnsnow Excel Discussion (Misc queries) 0 August 4th 06 07:20 AM
Specialized Cell Formatting myrddinbach Excel Discussion (Misc queries) 3 October 27th 05 07:17 PM
How do I link specialized reports in excel? tucksmom Setting up and Configuration of Excel 0 December 31st 04 04:47 AM


All times are GMT +1. The time now is 12:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"