add a hyphen on a 9 digit zipcode

[Chanoaguilas]

Hi guys,

I am new to using excel. so here's what I'm trying to do:
Some US zip codes have 9 digits and I want to a Hyphen "-" after the first 5 digits in a column. However, the same column also includes 5 digit ones so I am trying to use =IF(LEN(a2)>5,(REPLACE(a2,6,0,"-"))) but I can't seem to get it right. It should like this.

[TABLE="width: 152"]
<colgroup><col></colgroup><tbody>[TR]
[TD]21117-2111[/TD]
[/TR]
[TR]
[TD]21117[/TD]
[/TR]
[TR]
[TD]21234[/TD]
[/TR]
[TR]
[TD]20737-2073[/TD]
[/TR]
[TR]
[TD]21133-2113[/TD]
[/TR]
[TR]
[TD]21133-2113[/TD]
[/TR]
[TR]
[TD]20853[/TD]
[/TR]
[TR]
[TD]20850

[/TD]
[/TR]
</tbody>[/TABLE]
Can I have some help, please? Thank you.

 

[MARK858]

Can't you just concatenate as below?

Excel Workbook
	A	B
2	211172111	21117-2111
3	21117	21117
4	21234	21234
5	207372073	20737-2073
6	211332113	21133-2113
7	211332113	21133-2113
8	20853	20853
9	20850	20850
Sheet3

 

[MARK858]

If it is only the appearence you want to change then you could also just use conditional formatting using the formula =len(A2)>5 then under the custom number format use #####"-"####

 

[Rick Rothstein]

Here is another formula that you can consider...

=TEXT(A1,"[>99999]00000-0000;00000")