Custom Format with different Criteria

[Santhosh Mani]

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

 

[Duke Carey]

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]

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