You are here:
< Back

This can happen when Microsoft Excel sees a column of very long numbers (typically longer than 12-16 characters) that is not formatted as “text” column. Because the numbers are so long Excel tries to help by compressing the number into a “scientific notation” format. Once this data is compressed and *saved* over, there is a very slim chance you can recover the full number again without finding the original file of data.

Should you open a portfolio file from a client and notice a column is being formatted in this way, there are two methods you can use to try to “repair” the numbers.

  • The first method will work if your file is a .csv format and it is currently closed:
    1. Open a new document in Microsoft Excel.
    2. Click on the “Data” tab and then the “From Text” icon.
    3. Find and select your .csv file and click the “Import” button.
    4. This will bring up a window. Select “Delimited” followed by the “Next” button.
    5. Select the “Comma” checkbox only and click the “Next” button.
    6. Here is the important step; you can tell Excel what type of data is in your columns. At the bottom you need to select the column that contains the longer numbers. Once this column is highlighted, give it a format of “Text.”
    7. The selected column should show the word “Text” above it now instead of “General.”
    8. Once you have done this to all necessary columns, click the “Finish” button to load your data.
    9. Should the column still show incorrect numbers or scientific notation (ex. 1.4E+15) the file has likely already been corrupted. If you need this information in Simplicity, check with your client to see if they can provide you a clean file with the correct numbers.

 

  • The second method may work on a .csv, .xls or .xlsx file that is currently open:
    1. Select the column(s) with the issue, making sure the data becomes highlighted.
    2. Click on the “Data” tab and select the “Text to Columns” option.
    3. This will bring up a window. Select “Delimited” followed by the “Next” button.
    4. Select the “Comma” checkbox only and click the “Next” button.
    5. The column at the bottom should be highlighted already. If it is not, click on it to select/highlight it. Then select the format “Text” and then the “Finish” button.
    6. The numbers should now be in the correct format.

 

See anything missing or out of date on this page? Please contact Simplicity Support at helpdesk@simplicitycollect.com.