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

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

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