If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|