Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specialized custom number format | New Users to Excel | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
Creating a specialized worksheet for my credit cards. | Excel Discussion (Misc queries) | |||
Specialized Cell Formatting | Excel Discussion (Misc queries) | |||
How do I link specialized reports in excel? | Setting up and Configuration of Excel |