Zip Codes

[Guest]

I have a list of names and addresses in Excel. Some of the zip codes are in
just the regular 5 digit zip code format and some are in the zip + 4 format.
I would like to eliminate the additional 4 digits off the zip codes that have
them without having to manually delete those numbers from each cell. Is this
possible?

 

[Erin Searfoss]

There are easier ways if the zip codes are in a separate
column (take a look at Data, Text to Columns, using "-" as
a delimiter), but in case your zip code is included with
the rest of the address this formula will work provided
the zip code is the last item in each cell.

=IF(MID(A4,LEN(A4)-4,1)="-",LEFT(A4,LEN(A4)-5),A4)
where A4 is the cell with the address. You can add a
blank column next to your address column. Enter this
formula down the column, then copy the results and paste
as values over the original addresses.

Erin

 

[Mark]

Hi,
if you format the cells as Zipcode format, the other ones
can be formatteed as number only format or general.

Mark

 

[Guest]

I appreciate the responses and I feel like the answer is there. The problem
is, I'm new to excel, self-taught and don't know how to use the answers (if
that makes sense). I'm trying to figure out how to use the information in
the post from Erin Searfoss, but I'm not familiar with using delimiters.
I've encountered that before so I've familiar with the term, just not how to
deal with it. Any additional advice?