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.

21117-2111
21117
21234
20737-2073
21133-2113
21133-2113
20853
20850

<colgroup><col></colgroup><tbody>
</tbody>

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")