How to stop Excel from Auto-formatting and making it work like a number crunching program

Posted on

QUESTION :

I have a real problem with Excel in csv files where it is formatting numbers in certain ways and I cannot seem to get Excel to display the data right no matter what I do.

Imagine I have a colum called “Product Reference”. Within that column I have many IDs of the type:

  • 001145
  • 55666
  • 02133

Whenever I open the Excel file it formats these numbers to be:

  • 1145
  • 55666
  • 2133

Which is totally wrong. When I highlight the column and format it as text the previous 0’s at the beginning of the numbers do not return.

In an attempt to solve this I even made the csv file a text file first and then imported the file into Excel and specifically told it to not format the numbers by making it text format within the import but it still formats the numbers wrong when I close it and open it again due to the fact that csv cannot define types so Excel auto saves the file as pure garbage.

Has anyone found a way around this and could help me out?

Thanks,

ANSWER :

You need to specify in the csv file that it is text. You do this by putting your number in quotes and preceeding with and equal sign, eg:

="001145",="55666",="02133"

The easiest way to do this would be to do a find-replace on , with ",=", replacing end of lines (you might need to use an advanced editor like Notepad++ for this) with "rn=" and doing the start and end of the file manually.

  1. Change the file extension from “.csv” to “.txt” on the file you are having problems with.
  2. Open excel by itself. Don’t click on the file to open it.
  3. Click “Open”, change the file types to look for from “All Excel Files” to “All Files”
  4. Find your file and click open. Since Excel doesn’t auto format txt, it will then take you thru the wizard.
  5. Makes sure the “Delimited” radio button is selected. Click the “next” button.
  6. Check the “comma” delimiter box and keep hitting the “next” button until you SEE (but don’t click on it) the finish button.
  7. scroll thru each column in the spreadsheet, highlight each column(s) you want to manually format and change the format to “text”.
  8. Click the finish button.

Install OpenOffice from Oracle and do the same with OpenOffice’s CSV format and it works.

I’ve had this problem in Windows 7 Pro with Excel 2010. I used to be able to copy Outlook contacts to Excel, save as CSV and import to Gmail and Android (the mobile numbers with leading zeros and + signs).

To add to @Jon’s answer from this question, I had a CSV file that had several columns that had leading zeros and longer numbers that would either lose the leading zero or convert the long number to scientific notation, or both, when imported.

The only way I could fix was to first format all of the cells in the empty sheet to Text, by using the following steps:

  1. Select all the cells in the sheet:

enter image description here

  1. Change the format of every cell:

enter image description here

After formatting all of the sheet cells to be Text, then I could import the CSV using the following steps:

  1. From the Data tab, click From Text:

enter image description here

  1. Select your .txt or .csv file and click Import

  2. Select Delimited and click `Next:

enter image description here

  1. Select the appropriate delimiter (I chose Comma) and deselect any others that don’t apply, then click Next:

enter image description here

  1. Scroll to the right to find the column(s) that need(s) to be formatted as Text to retain leading zeros and to prevent scientific notation. Click the column (it should darken), then select Text in the option list. Be sure to select all columns that need to be correctly formatted:

enter image description here

  1. Then click Finish. If you scroll to the columns that you formatted, you’ll see that all leading zeros are retained and that long strings of numbers are not in scientific notation.

Hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *