HANDLING CELL NUMBERS IN csv formatGeneral


Last Updated: 2012-10-24 11:46:24
  1. captgnvr

    captgnvr Member

    Dear all

    After importing Google contacts, find most of the numbers have lost the zero. whereby if i am out station, i have to edit the number by adding zero and then dial.

    I opened this csv file in excel and formatted the cell number column as text and added zero to all the required number. Saved the file as text file and named it as text 'new.csv'.

    Deleted all contacts in my Samsung note and imported from Google contact.

    Surprised to see that all zeros are gone in the contacts.

    Went back to see the new.csv file and found it is saved without the zeroes.

    So pls guide me in how to edit the csv file so that the zero is saved.

    Advertisement
  2. PiscesCloud

    PiscesCloud Well-Known Member

    I can tell you how to edit the .CSV file but I don't know if it will work when imported into Google contacts.

    If a number is entered in Excel in numerical format the leading zeros are removed. If numbers are entered in text format the leading zeros are retained. To change from numerical format to text format select the column of numbers, right click on it and select 'Format Cells'. Click on the 'Number' tag and select 'Text'; that's it. I suggest you do a 'Save As' with a new file name.

    Alternatively, if you would like to experiment with just 1 or 2 contacts you can either repeat the above but with the cells selected instead of the column, or simply edit a cell with '0 instead of a 0 by itself. Excel treats all non-numerical characters such as a ' as text; also the ‘ character is special in Excel because it is not visible when you move away from that cell.

    I’d be interested to hear how you get on with it.
  3. captgnvr

    captgnvr Member

    GOOD DAY MR PiscesCloud

    Thanks for your response.

    As I am sailing out today anytime soon, I may not be able to reply after trying out what u hv instructed owing no internet facility at sea.

    However in another one week I will be proceeding on leave and in the mean time I will try it out and inform you. thanks once again.
  4. captgnvr

    captgnvr Member

    D/Mr. PiscesCloud

    luckily got some few minutes, tried as you hv mentioned which ref my thread also was done previously also but when I reopen the csv file the zero is not seen in the saved file.

    I did use 'save as'; excel 97-2003; file type as csv and click save. It asks for the option to click 'yes' if you want to retain the format etc and I selected 'yes'. after saving it when I open the file the zero is gone.

    Pls sugest! what needs to be done and what i am doing is wrong.
  5. PiscesCloud

    PiscesCloud Well-Known Member

    Hi captgnvr

    I trust you've had a good week at sea.


    Your reply was a bit of a surprise! I confirmed your result and have done some digging around to find the answer. Rather than repeat it at considerable length here I suggest you read the following link.


    http://excel.tips.net/T002588_Handling_Leading_Zeros_in_CSV_Files.html


    Unless you have very many numbers to correct you may well conclude it would be easier to simply modify the numbers manually after importing them! I’d be interested to hear how you get on with it.

Share This Page