Leading zero conversion problem

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.

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!

In article ,
"Robert Judge" wrote:

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.

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

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

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

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

Nick


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

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
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"NickMinUK " wrote in message ...
Suggest using ASAP Utilities (freeware from www.asap-utilities.com)

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

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

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

Nick


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

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?


-----Original Message-----
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!

In article ,
"Robert Judge"
wrote:

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