Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Stop Losing Zeroes on phone numbers from XLS > CSV format

Nov 13, 2010 9:46PM PST

*how can i stop phone nubmers being converted into rubbish when going form Excel to CSV?

AIM
I have just tidied up my bloated contact list in excel.
i want to upload to google contacts / outlook as a .CSV file.

PROBLEM
when i do so, the phone numbers shift into a different format. example a british mobile number like
0044 77190 19091 is switched into 4.47719E+11,
which my phone can not then read when uploaded.

I HAVE TRIED SO FAR...
...pressing ctrl+1 and selecting format as text
...opening the excel file and 'save as' to a comma delimited file
...concatenating to add spaces in front and at the end of the numbers in excel, so that the CSV file might see them as text
...saving the numbers only into a new .txt file, then going into the .CSV file and using the data > 'get external data' > import from text.

RESULT SO FAR
in all instances i can get the numbers in the right format in the CSV file, but when i come to save, i get a message saying that some of my preferences might not be saved. when i reopen the CSV file, the numbers are back in the dud format.


would be very grateful to any experts who can help. it is driving me nuts.

Running Windows 7, Ofice/Excel 2010

Discussion is locked

- Collapse -
Re: phonenumbers to csv
Nov 13, 2010 10:15PM PST

Works perfect with me. This is how I do it.
1. I type the phonenumber as text by starting with a quote: '001234567891
2. I export to csv-format
3. I open the csv-file with Notepad and look at the result. It's 001234567891

What I don't do: open the csv-file with Excel. That's because I know Excel displays such long numbers in the scientific format. I don't care about how Excel shows it. I just look in the file with Notepad to see what's really in it and I am happy with the result.

But I don't know, of course, how your phone imports this perfect csv-file. It might be necessary to add quotes around the number in the csv-file: "001234567891" to tell the phone it's text, not a number.

Kees

Kees

- Collapse -
Problem sorted
Nov 14, 2010 3:27AM PST
- Collapse -
HELP!!!
Feb 16, 2014 11:28AM PST

Hi jonty,

I know this is a ridiculously old post, and I've had most of my problem (which was the same as yours) solved --- except the last bit: Getting the numbers into my phone in a usable format.

Importing to gmail works fine and then my phone syncs all the contacts, all very nice. But when I open the contact in my mobile, the mobile phone number (although displayed correctly with the zero and no spaces) is displayed under "notes" =/ This is so annoying! Because of this I can't select any of my imported contact numbers if I want to send them a text - they simply dont show in the "send to" field when typing an SMS.

Can you please help me fix this? How did you manage back in 2010? =p

Windows 7, Excel 2007, Android / Samsung Galaxy Ace

- Collapse -
Loosing phone number format when saving xls to csv format
Nov 13, 2014 7:38PM PST

Hi guys,

i know this is a repeat...but I am stuck with this.

i have an xls with some phone numbers of 12 digit ( eg: 902123456789). when i save it as csv it turns into scientific format(9.02166E+11 ). i tried out options listed in this forum like prepending it with " ' " and opening it in wordpad. when i open this csv in wordpad i am able to see the correct number ie in correct format. but my issue is i need to upload this csv file into a tool and when i do this i get a prompt from tool that invalid format of phone number.

i think what is happening is that the number is in correct format (in formula bar) when since its format is scientific in the cells i am getting error. any suggestions to solve this will be really appreciated.

TIA

- Collapse -
Re: phone number format
Nov 13, 2014 7:54PM PST

If in the csv-file you see the correct number between the comma's so (...,902123456789,...) it's a correct csv-file. Then back to the manual of the tool to see why it finds it invalid and what it needs instead.

Kees