Zip Code fields

[Kandicevc]

How can I eliminate the -xxxx from 5+4 zipcodes so I can sort on the first five numbers. Some numbers in this field on the spreadsheet are 5 and some are 5+4. Need to quickly change all to 5

 

[CyberTaz]

Hi Kandice -

Here's one option - assuming you *do* want to sacrifice the -xxxx;

1- insert a range of cells equal to the number of records (or a new column)
adjacent to the current zips,

2- In the first new cell (we'll call F5 with the existing zips starting in
cell E5), format for Zip Code & enter the formula: =LEFT(E5,5)

3- Copy the formula down to the last row occupied by a record & leave those
cells selected,

4- Copy the selected cells, then go to Edit> Paste Special - Values, OK

That should retain the first 5 characters from each of the original zips in
the newly added cells. You can then delete the cells/column containing the
original zips or cut/paste it to another location for future reference.

HTH |:>)
Bob Jones
[MVP] Office:Mac