Removing extra digits from a zip code

[kbiro]

Hello,

I have a large spread sheet filled with customer info including their address with zip code. Depending on how the data was entered, some zip codes are only five digits, and others have the extra four with the hyphen.

How do I remove the extra figures in the other zip codes without going through 10s of thousands of lines?

Thank you in advance.

[AlKey]

You can use this formula to get part that you need from the left.

in B2 and copy down

=TEXT(LEFT(A2,5),"00000")

Row\Col	A	B
1	Zip
2	60004	60004
3	60004	60004
4	60004	60004
5	60004-1557	60004
6	60004-1802	60004
7	60004-2084	60004
8	60004-2530	60004
9	60004-2530	60004

[kbiro]

Thanks! That worked! I also tried text to columns with using the hyphen as the separator and that gave me the desired result as well.

[AlKey]

Thank you for the feedback!

There is another benefit of using TEXT function. It will add missing leading zeros if needed.