Zip codes and Excel - Please Help!

  • Thread starter Thread starter rmeffert
  • Start date Start date
R

rmeffert

Here is my issue: I have 19k zip codes in excel. They where sent withou
the 0's to me. So many of the zip codes are only 3 or 4 digits instea
of 5...ex (00235 looks like 235)

So I went in and formatted the cells as a zip code digits. Whic
visually corrected the field to display all 5 digits.

However I am using a macro to cut and paste from excel into anothe
program (text field) and the macro cuts the cell and pastes but th
cells value is still 235 even though it is visually 00235 in the cel
so the macro pastes 235 and the zip code is not correct. I know that i
I manually went in and put a '0 or '00 infront of the zip codes that i
would correct the situation. But I dont have the time to do that fo
19k zip codes. Can someone please assist?

Thanks
Rya
 
1) Open a new Access file
2) Save your Excel file as a .csv file
3) Import the .csv file into Access
4) Run an update query in Access:

Update to RIGHT('00000'+ Zip Code field,5)
or 9 if using long Zip Code

5) Copy access table and paste into Excel and SAVE AS .CSV
...... if saved as .xls You will lose the 0's again.
 
First thank you so much for taking your time to assist me. The proble
is we dont have access on our machines here at work. Is there anywa
you can do this in excel itself, or even utilize notepad or word
because I have these systems. Thanks
 
i tried it using your sample

what i did is
set the format of the column into zip code

then i inserted a new column
i copied the zipcode column to new column
pasting special with options values and number format.
then i saved it as CSV file...

hope this helps...
 
This formula will convert the number to a string with the desired 0's

The number is in D1 and the formula below is in E1
=RIGHT(CONCATENATE("00000",TEXT(D1,0)),5)

Once you copy the formula in every cell you can copy the formula colum
and "paste special" choosing "value" to another column. Delete th
formula column and you have a text string for zip codes which is wha
you want anyway
 
Back
Top