Home » Category » Microsoft Excel

Microsoft Excel: Zip codes and Excel - Please Help!

205| Wed, 04 Jun 2008 12:01:00 GMT| anonymous| Comments (5)
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

--
Message posted from http://www.ExcelForum.com

Keywords & Tags: zip, codes, excel, microsoft

URL: http://www.developertags.com/microsoft-excel/495106/
 
«« Prev - Next »» 5 helpful answers below.
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.

>--Original Message--
>Here is my issue: I have 19k zip codes in excel. They
where sent without
>the 0's to me. So many of the zip codes are only 3 or 4
digits instead
>of 5...ex (00235 looks like 235)
>So I went in and formatted the cells as a zip code
digits. Which
>visually corrected the field to display all 5 digits.
>However I am using a macro to cut and paste from excel
into another
>program (text field) and the macro cuts the cell and
pastes but the
>cells value is still 235 even though it is visually 00235
in the cell
>so the macro pastes 235 and the zip code is not correct.
I know that if
>I manually went in and put a '0 or '00 infront of the zip
codes that it
>would correct the situation. But I dont have the time to
do that for
>19k zip codes. Can someone please assist?
>Thanks
>Ryan
>
>--
>Message posted from http://www.ExcelForum.com/
>.
>

gregs | Wed, 04 Jun 2008 12:02:00 GMT |

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
--
Message posted from http://www.ExcelForum.com

rmeffert | Wed, 04 Jun 2008 12:03:00 GMT |

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...

>--Original Message--
>First thank you so much for taking your time to assist
me. The problem
>is we dont have access on our machines here at work. Is
there anyway
>you can do this in excel itself, or even utilize notepad
or word,
>because I have these systems. Thanks!
>
>--
>Message posted from http://www.ExcelForum.com/
>.
>

mackie | Wed, 04 Jun 2008 12:04:00 GMT |

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
--
Message posted from http://www.ExcelForum.com

ogaebe | Wed, 04 Jun 2008 12:05:00 GMT |

That worked perfectly (formula). Thank you so much! You saved m
--
Message posted from http://www.ExcelForum.com

rmeffert | Wed, 04 Jun 2008 12:07:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories