| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Format with different Criteria
I need to format a complete column of cells as below. I tried format Cell
Number Custom but accepts only two criteria, not all eight criteria listed below. [=1000] ####-"000";[<=9999] ####-"000"; [=10000] ####-"00"#;[<=99999] ####-"00"#; [=100000] ####-"0"##;[<=999999] ####-"0"##; [=1000000] ####-###;[<=9999999] ####-### Is there another solution? I am not familiar with coding, so if coding is the only solution please give me an appropriate one, Thanks and regards Santhosh Kuwait |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Format with different Criteria
Rather than formatting the cells, insert a helper column adjacent to the one
you've been trying to format. Then use this to display the data the way you want. Assumes your data is in column A, starting in row 2. Copy down as needed. Then Hide the column with the original values =TEXT(VALUE(LEFT(A2,4)&REPT("0",7-LEN(A2))&RIGHT(A2,LEN(A2)-4)),"0000-000") "Santhosh Mani" wrote: I need to format a complete column of cells as below. I tried format Cell Number Custom but accepts only two criteria, not all eight criteria listed below. [=1000] ####-"000";[<=9999] ####-"000"; [=10000] ####-"00"#;[<=99999] ####-"00"#; [=100000] ####-"0"##;[<=999999] ####-"0"##; [=1000000] ####-###;[<=9999999] ####-### Is there another solution? I am not familiar with coding, so if coding is the only solution please give me an appropriate one, Thanks and regards Santhosh Kuwait |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Custom Format with different Criteria
ThankQ for your reply. But I dont want to use another column and keep my
sheet grow. This formatting I want to use in a form where user enter numbers in a column ( say Col C) and the adjacent column (Say Col D) will lookup this number and bring lookup value from a list. When the form is printed the all numbers entered in Col C will look in same structure. That's the idea. Santhosh "Santhosh Mani" wrote: I need to format a complete column of cells as below. I tried format Cell Number Custom but accepts only two criteria, not all eight criteria listed below. [=1000] ####-"000";[<=9999] ####-"000"; [=10000] ####-"00"#;[<=99999] ####-"00"#; [=100000] ####-"0"##;[<=999999] ####-"0"##; [=1000000] ####-###;[<=9999999] ####-### Is there another solution? I am not familiar with coding, so if coding is the only solution please give me an appropriate one, Thanks and regards Santhosh Kuwait |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ignore auto format after applying a custom format?? | Excel Discussion (Misc queries) | |||
| Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
| how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
| Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
| Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
Hybrid Mode
