If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Leading zero conversion problem
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. |
#3
|
|||
|
|||
Leading zero conversion problem
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/ |
#4
|
|||
|
|||
Leading zero conversion problem
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/ |
#5
|
|||
|
|||
Leading zero conversion problem
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. . |
Thread Tools | |
Display Modes | |
|
|