Excel Truncated Student ID's

Question

Good afternoon all,

I have been provided a CSV file which contained Student ID's amongst other data. This co-worker did not back this file up and is the original.

This co-worker viewed the CSV file in Excel and proceeded to save it back to the CSV. However in doing several of the Student ID's have been replaced with the truncation as follows.

1.11E+06

5.01E+03

1.00E+09

Is there anyway to revert the truncated student ids to the original?

Thanks,

-4Runner1982

Answer 1

Try this:

• Select your student IDs
• Right-click, choose "Format Cells"
• In "Number" tab, under category, choose "Number".
• For "Decimal places", enter 0
• Click OK

Regards,

Edwin Tam

Excel Power Expander - http://www.vonixx.com

Answer 2

4Runner1982 wrote:

I have been provided a CSV file which contained Student ID's amongst other data. This co-worker did not back this file up and is the original.  This co-worker viewed the CSV file in Excel and proceeded to save it back to the CSV.  However in doing several of the Student ID's have been replaced with the truncation as follows.

1.11E+06

5.01E+03

1.00E+09

Is there anyway to revert the truncated student ids to the original?

You are probably SOL.  But before giving up, check some details.

Note that Excel merely chooses to display some numbers in the form 1.11E+06.  The actual value might not have been changed.  (One can hope!)

Format the cell as Number with zero decimal places.  Is the value really changed?

I'm afraid it probably is, based on the sequence of events that you describe.

In the future, it would behoove you and your coworker to change the cell format before saving to a CSV file.  And it would behoove you to import the CSV file data using an Import Text wizard, which allows you set a column of data to format Text, instead of opening the CSV file directly.

That said, I am surprised that Excel displayed the data as 1.11E+06, 5.01E+03 and 1.00E+09 in the first place.  Normally, those numbers fit into the default column width formatted as General.  The General format reverts to the Scientific number form under some circumstances when the number of significant digits exceeds 11.

Also, keep in mind that if you enter a student ID as a bona fide number, Excel will truncate after the 15th digit, substituting zeros.

"Numeric" IDs should be entered as text, not numbers, in order to prevent Excel default interpretation and manipulation of numeric data entry.  To do that, either format cells as Text before entering the data, or type an apostrophe prefix before each data.  (The apostrophe will not be displayed, and it is not interpreted as part of the data value.)

If you have any further questions, I suggest that you post the URL of an example CSV file that you uploaded to a file-sharing website.  Of course, the data should be contrived, and any private data should be removed or modified.  The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

RapidShare: http://www.rapidshare.com