How can I shorten excess digits from zip codes in multiple cells?

[bottledwater]

Hi there,

I have a list with thousands of addresses. Some of them have these +4 zip codes, unfortunately no with a dash.
It looks like this: Shreveport, LA 711096512
Does anyone have an idea how to shorten these zip codes to only 5 digits or how to insert a dash after the 5th digit?

[FlameRetired]

You have Excel 2013. Try Flash Fill. It's in the Data tab of the ribbon.

With list in column A be sure to be in the next column over ... B.

Just type in the first 5 digits of the first zip code, hit Flash Fill. You'll get a 'fly-out' icon prompting you to accept/undo.

[David A Coop]

In Custom Formatting there is a Zip+4 option (under Special) which will insert the "-" after the fifth digit.
(Right click on the cell(s), click Format Cells, Click Special)

However, you said SOME. This will NOT work for those cells that are not 9 digits long. You may need to use the LEFT() formula. viz LEFT(A1,5) in cell B1 (where your zip code is in A1. This will work for all cells, returning the 5 digits of the ZIP code.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[bottledwater]

Thank you both for your answers.
I already tried the LEFT formula but it didn't work for some reason.
I'll try the flash fill tomorrow. Thanks again!

[FlameRetired]

You're welcome and thanks for the feedback.

[David A Coop]

You're welcome bottledwater, but why didn't the LEFT formula work?

I assumed that the ZIP Code was in a column by itself. Is the whole City, State and Zip in the one column?

So if you have Shreveport, LA 711096512 in Cell A1, then =MID(A1,FIND(",",A1)+5,5) will extract the 5 digit code you want.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[FlameRetired]

Does anyone have an idea how to shorten these zip codes to only 5 digits or how to insert a dash after the 5th digit?

Edited As an afterthought I included TRIM.

This does all the above. Try it in B1 as in below example.

=TRIM(CHOOSE(((LEN(A1)-LOOKUP(25^25,--FIND(" ",A1,ROW($1:$50))))=9)+1,MID(A1,LOOKUP(25^25,--FIND(" ",A1,ROW($1:$50)))+1,99),REPLACE(MID(A1,LOOKUP(25^25,--FIND(" ",A1,ROW($1:$50)))+1,99),LEN(MID(A1,LOOKUP(25^25,--FIND(" ",A1,ROW($1:$50)))+1,99))-3,0,"-")))

Row\Col	A	B
1	Shreveport, LA 711096512	71109-6512
2	Shreveport, LA 711096512	71109-6512
3	Shreveport, LA 711096512	71109-6512
4	Shreveport, LA 71109-6512	71109-6512
5	Shreveport, LA 711096512	71109-6512
6	Shreveport, LA 61920	61920
7	Shreveport, LA 711096512	71109-6512
8	Shreveport, LA 711096512	71109-6512
9	Shreveport, LA 618736512	61873-6512
10	Shreveport, LA 711096512	71109-6512
11	Shreveport, LA 711096512	71109-6512
12	Shreveport, LA 61832-6512	61832-6512
13	Shreveport, LA 711096512	71109-6512
14	Shreveport, LA 71109	71109
15	Shreveport, LA 711096512	71109-6512
16	Shreveport, LA 711096512	71109-6512
17	Shreveport, LA 711096512	71109-6512
18	Shreveport, LA 711096512	71109-6512
19	711096512	#N/A
20	71109-6512	#N/A
21	71109	#N/A