Changing zip code format

Shearman

New Member
Joined
Oct 10, 2006
Messages
19
I have about 4,000 rows of information and one of the columns is for a client's zip code. The current format is *****-****, and I need to change them all to read only the first 5 digits. Is there a way to do this with existing information, or do I need to go into each cell and manually delete the last 4 digits?

I know I can remove the - by using control-H, but I'm stuck on removing the last 4 digits. I tried Validation to fomat the cells to only accept whole numbers from 00000 to 99999, but that did not change anything. Hopefully someone will have some good news for me. :rolleyes:

Thanks!!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323
Can't you just insert a column and use =left(a1,5) to get the first 5 digits?
 

Shearman

New Member
Joined
Oct 10, 2006
Messages
19
Thanks for the formula.., I learned something new. However, I need the spreadsheet in a particular format (It's for loading information into a Dialer), so if I do that for the entire column, I cannot then remove the column with the incorrect zip code format since the formula is referencing those cells.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
copy the collumn with the formulas, paste special / values to the collumn you want the new zip code to be in.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,847
Messages
5,544,637
Members
410,627
Latest member
georgealice
Top