up vote 0 down vote favorite
share [g+] share [fb]

On my website I generate a CSV file from a database of users. When I view the CSV file in my browser all the information comes up as it was submitted by the users, but when I download the CSV file and open it in Excel, the phone number column seems to corrupt somehow. For example, below is the csv data for a single user, as viewed in my browser:

"11","Daniel","Nuttall","xx Alder Street","","HUDDERSFIELD","West Yorkshire","HD1 6xx","xxx@xxxxxxx.co.uk","447769697410","item 2","50","on","on"

But when I open this in Excel, the phone number changes to: 4.4777E+11

All the other info stays the same, its just the phone number that changes. Anyone know why this happens?

link|improve this question
feedback

1 Answer

up vote 3 down vote

Excel chooses a wrong format for the cell. Rightclick the cell and choose "Format Cells...". Choose "Text" to get plain text. You might want to do this first for the whole table.

Format Cells

link|improve this answer
Thanks for the response. This hasn't changed the value in the spreadsheet. However, I have noticed that the correct value shows up in the 'fx' bar when you select the cell. So why does it still show up as 4.4777E+11 in the spreadsheet? – nutman Nov 8 '11 at 12:58
I resolved it by changing the format to 'number' and removing the decimal places so all is good now, but I'd still like to know what causes it to change to a somewhat random value of 4.4777E+11 – nutman Nov 8 '11 at 13:00
1  
@nutman - It's not a "somewhat random number". 447769697410 is 447,769,697,410 with the thousands separator so you can see it's 4.47769697410 * 10E+11 which gets truncated to what you see – ChrisF Nov 8 '11 at 13:10
If you don't format a field with a large number, Excel will assume scientific notation (i.e. 4.4777E+11) for any large numbers. Another solution would be to output the phone numbers with some sort of formatting already added so that Excel sees it as a text field rather than a number field by default. – BBlake Nov 8 '11 at 13:14
oh god yeh i didn't even realise it was a ten to the power x number lol. Was just viewing it in my mind as a phone number. Thanks for clearing that up for me. – nutman Nov 8 '11 at 13:16
show 1 more comment
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.