Office & Productivity Software forum

General discussion

Stop Losing Zeroes on phone numbers from XLS > CSV format

by jonty1982 / November 13, 2010 9:46 PM 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
You are posting a reply to: Stop Losing Zeroes on phone numbers from XLS > CSV format
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Stop Losing Zeroes on phone numbers from XLS > CSV format
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: phonenumbers to csv
by Kees_B Forum moderator / November 13, 2010 10:15 PM 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
by jonty1982 / November 14, 2010 3:27 AM PST
Collapse -
HELP!!!
by carouseLP / February 16, 2014 11:28 AM 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
by anukriti_verma / November 13, 2014 7:38 PM 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
by Kees_B Forum moderator / November 13, 2014 7:54 PM 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

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!