MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replacing variables


Posted by Iridius on May 08, 2001 12:40 PM

I have a column of zip codes, ranging from 98001
to 98529. I want to replace the variable zip codes,
converting them, e.g. 98001 to "98001", adding the
quotations. When I do a replace of ????? with "?????"
get "?????" rather than "98101". Suggestions?

I tried a macro, but it kept converting them all to the
first zip code.


Posted by Barrie Davidson on May 08, 2001 12:47 PM

If you want to change the zip codes from number to text, an easy way to do this is:
Highlight the data, select Data|Text to Columns. Then choose fixed width, make sure you only are parsing one column. Choose next, the last option is "Column data format". Select "Text" and your numbers will be changed to text.

Hope this helps you out.

Barrie

Posted by Mark W. on May 08, 2001 12:49 PM

Do you really want to add quotes or are you trying
to change the data type from numeric to text?

Posted by Iridius on May 08, 2001 12:50 PM

Yes I really do. I'm going to use them in a replace
that involves the quotations.

Posted by Iridius on May 08, 2001 12:51 PM

Actually I know how to do that. I want to add quotations
to either side to do a more complex replace. Thank you
though!

Posted by Aladin Akyurek on May 08, 2001 12:52 PM


=CHAR(34)&A1&CHAR(34)

where A1 contains the first zip. Copy down this as far as needed.

Aladin

Posted by Barrie Davidson on May 08, 2001 12:54 PM

Okay, how about putting the following formula beside the zip codes
=""""&A1&""""
and then copying/pasting as values only.

Barrie

Posted by Iridius on May 08, 2001 1:01 PM

YES IT WORKS! Thank you BARRIE!