Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbers convert to formula in csv
I can't get a column of 16 digit numbers to transfer when converting the file
from an xls to a csv file. The cells in the csv appear to be a scientific formula (5.19E+15, 4.27E+15, etc.). When actually clicking in the cell, the number displays correctly except for the last digit which displays as a 0. I think this might have something to do with the +15 that's in the formula since the number that should appear is 16 digits long. I've tried formatting the column in the xls file as text, general and number (without decimal) and end up with a variety of results when saving and then opening the csv. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbers convert to formula in csv
Numbers in Excel are limited to 15 significant figures. If you want a 16
digit number, you need to format the cell as text *before* entering the 'number', or precede the 'number' by an apostrophe to keep it as text. Remember that if you save such a text string in a csv file it will be correctly saved (as you can see if you open the csv with Notepad), but if you merely open the csv with Excel, it will again be treated as a number, so will be back to 15 significant figures. If you do want to open a csv with Excel without it getting garbled, do so not with File/ Open but with Data/ Import External Data, and make sure that in the text import wizard you specify the relevant columns as being text. -- David Biddulph "SteveJ" wrote in message ... I can't get a column of 16 digit numbers to transfer when converting the file from an xls to a csv file. The cells in the csv appear to be a scientific formula (5.19E+15, 4.27E+15, etc.). When actually clicking in the cell, the number displays correctly except for the last digit which displays as a 0. I think this might have something to do with the +15 that's in the formula since the number that should appear is 16 digits long. I've tried formatting the column in the xls file as text, general and number (without decimal) and end up with a variety of results when saving and then opening the csv. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbers convert to formula in csv
Problem solved. I had previously formatted the cells as text before saving
to csv and still had the problem, but I've just redone it and everything worked as expected. I must have errored the first time I did it. Thank you for your help. "David Biddulph" wrote: Numbers in Excel are limited to 15 significant figures. If you want a 16 digit number, you need to format the cell as text *before* entering the 'number', or precede the 'number' by an apostrophe to keep it as text. Remember that if you save such a text string in a csv file it will be correctly saved (as you can see if you open the csv with Notepad), but if you merely open the csv with Excel, it will again be treated as a number, so will be back to 15 significant figures. If you do want to open a csv with Excel without it getting garbled, do so not with File/ Open but with Data/ Import External Data, and make sure that in the text import wizard you specify the relevant columns as being text. -- David Biddulph "SteveJ" wrote in message ... I can't get a column of 16 digit numbers to transfer when converting the file from an xls to a csv file. The cells in the csv appear to be a scientific formula (5.19E+15, 4.27E+15, etc.). When actually clicking in the cell, the number displays correctly except for the last digit which displays as a 0. I think this might have something to do with the +15 that's in the formula since the number that should appear is 16 digits long. I've tried formatting the column in the xls file as text, general and number (without decimal) and end up with a variety of results when saving and then opening the csv. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert numbers (quantities) en letters with a formula? | Excel Discussion (Misc queries) | |||
How do I convert formula result into numbers so I can sum results? | Excel Discussion (Misc queries) | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
How do I write a formula to convert numbers to written text? | Excel Worksheet Functions | |||
Is there a formula to convert numbers to written text in Excel? | Excel Worksheet Functions |