MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.