How to add an * to the end of each zip code
Posted by Deirdre on May 11, 2001 12:23 PM
Hi, I know this must be hilariously easy - but I cannot find the answer. Using zip code finder software, I have exported all the zip codes for Northern California into a column in Excel so that I can add an * (to include all 9 digit areas) to the end before copying them to a query in Access. For example, I want 92134 to become 92134* without having to do it manually?
I thought I would be able to use Search & Replace. However, each of the 2500 zip codes is different. how do I easily add an * the the end of each one without having to manually type it? Thanks. Deirdre
Posted by Aladin Akyurek on May 11, 2001 12:33 PM
What about using a formula?
=A1&"*" [ copy down as far as needed ]
Copy the result column do a Paste Special|Values in column A where I assumed your original zip codes to be.
Aladin
Posted by deirdre on May 11, 2001 12:47 PM
Thanks- but I do not understand answer
Aladin,
Thanks for the quick response. However, where do I insert the forumula? All my zip codes are in the "C" column. Using your forumula, won't that just give me the values for C1?
Deirdre
become 92134* without having to do it manually? : I thought I would be able to use Search & Replace. However, each of the 2500 zip codes is different. how do I easily add an * the the end of each one without having to manually type it? Thanks. Deirdre
Posted by Aladin Akyurek on May 11, 2001 12:54 PM
Re: Thanks- but I do not understand answer
Deirdre,
Is column D free? If so,
in D1 enter: =C1&"*"
and copy down this to the last row where you have still a zip code.
Then copy all values in D and do Edit|Paste Special|Values over C.
Aladin
,
Posted by Deirdre on May 11, 2001 12:54 PM
I got it! Thanks a bunch..
Duhhh......I finally figured it out based on your first answer. I need to go back to Excel 101. Thanks. Deirdre What about using a formula? become 92134* without having to do it manually? : I thought I would be able to use Search & Replace. However, each of the 2500 zip codes is different. how do I easily add an * the the end of each one without having to manually type it? Thanks. Deirdre
Posted by Mark W. on May 11, 2001 1:06 PM
Aladin, Deirdre... Here's another way...
I've now taken a serious look at the *FREE* ASAP
Utilities download at http://www.asap-utilities.com/ .
It is very powerful! With this utility all Deirdre
would have done is format her zipcodes as 00000"*",
select the cell range and choose...
ASAP Utilities | Range | Convert cell's value to it's formatted look
...and Voila! Done.
Take a look when you get a chance.
Posted by Aladin on May 12, 2001 2:07 AM
ASAP utilities...
gives a compile error with Office 2001 for Mac, I believe, in Menu.
Aladin
I've now taken a serious look at the *FREE* ASAP become 92134* without having to do it manually? : I thought I would be able to use Search & Replace. However, each of the 2500 zip codes is different. how do I easily add an * the the end of each one without having to manually type it? Thanks. Deirdre
Posted by Mark W. on May 13, 2001 7:48 PM
Re: ASAP utilities...
Bummer! I didn't upgrade from Office 98 so I
can't reproduce your problem. Why don't you
email Bastien Mensink (bastien@asap-utilties.com)
and describe your difficulties? gives a compile error with Office 2001 for Mac, I believe, in Menu.
Posted by Mike on July 06, 2001 8:49 PM
Re: ASAP utilities...
If I wanted to convert zip codes to have an asterisk at the end of each...
Assuming column A1 has a zip code, I would go to column B1 and enter the following formula =concatenate(A1,"*")
If you want to have data only (which I assume you do), copy B1 and use "paste special / values" to paste the value into column C.
Repeat for all pertinent rows.
Sorry if this sounds confusing. It isn't.
