Leading zero conversion problem

[Robert Judge]

I have exported data from Outlook 2002 Contacts into an
Excel 97 file. In the export process, all the leading
zeros in the zip codes have become "'". For example, a
Contacts zip code of 01075 has become '1075 in Excel. I
tried using Excel "Find and Replace" to replace "'"
with "0", but Excel can't find the "'" in the spreadsheet,
although it shows in the cells.

How can I fix this? I will appreciate advice.

 

[Domenic]

Hi Robert,

If you mean that the "'" shows in the formula bar, but not in the actual
cells themselves, then assuming that your zip codes are in Column A, try
using a helper column as follows:

1) Put this formula in a helper column and copy it down as far as you
need to
=0&A1
2) Select the data in your new helper column
3) Edit > Copy > Edit > Paste Special > Values > Click Ok
4) Delete the old column

Hope this helps!

 

[NickMinUK]

Suggest using ASAP Utilities (freeware from www.asap-utilities.com)

The number imported will not actually be a number, it will be text. S
using ASAP, first delete leading characters (Text/Delete number o
leading Characters, select 2). This will remove the "" from all number
within selected range.

Then convert text number to number (Numbers/Convert text numbers t
numbers).

Then reformat the number (from within excel format menu) to show
leading zero (custom format 0####)

Nic

 

[David McRitchie]

I would suggest that you convert all zip codes to text, not numbers.
They if you have to sort by zip code you can sort 5 digit along with
zip+5 and even zip codes from other countries.

See fixuszip5 macro in
http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5

The format Nick nick supplied of 0#### is incorrect
it should be 00000 i.e. 33 would be 033 not 00033
But the macro above will do everything at once as described
on the web page. Some Carribean countries have 3 digit
zip codes. i.e. 00777
--

 

[Robert Judge]

Thank you, that worked. Now I have a different but
similar problem. I have imported an EXCEL file
containing names and zip codes into Outlook 2003
Contacts. However, Outlook has dropped the leading zero
in the zip code. So, while the EXCEL file has a zip code
of 01075, after the import, Outlook Contacts shows the
zip code as 1075. Do you have a fix for that problem?