How can you make Excel 2007 stop formatting cells?

Question

I export my transformed records to a CSV Flat File.

The records contain a 'string' column only digits (-no alphabetic characters), an example value is 0410.623.764 or 0789.457.142

After opening the CSV file using MS Excel, the value becomes 410.623.764 Now I want to keep the leading 0 when opening the file in Excel.

So, the problem occurs when opening the CSV with MS Excel.

UPDATE: When opening with Notepad, I see the leading '0' in 0410.623.764

UPDATE 2: I found some workarounds in Super User but I'm not satisfied at all. Because I don't see myself applying a 2-minutes-long workaround again and again. Since I generate new (destination) files every time I receive new source files.

My question is: Is there a programmatical approach to stop formatting in Excel?

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

There is nothing wrong with your flow or your output. Excel is "removing" it for display and use in formulas.

You can configure excel to leave the leading zeroes. Here's the MSDN http://office.microsoft.com/en-001/excel-help/keep-leading-zeros-in-number-codes-HA010342581.aspx

There are some ways to ensure that Excel maintains leading zeros. These are discussed in this question:

Add leading zeroes/0's to existing Excel values to certain length

The TEXT function will, among other things, allow you to retain the leading zeros.

Here's another interesting link:

http://chandoo.org/wp/2012/02/15/use-text-format-to-preserve-leading-zeros/

edited May 23 '17 at 12:05

Community

1
1

answered Nov 14 '13 at 12:35

TsSkTo

1,412
13
25

This is a temporary answer to my question. It would be better if there is a programmatical approach. – Tassisto Nov 28 '13 at 14:22
How about something like this? http://stackoverflow.com/questions/3992541/add-leading-zeroes-0s-to-existing-excel-values-to-certain-length – TsSkTo Nov 28 '13 at 14:26
Bingo: http://chandoo.org/wp/2012/02/15/use-text-format-to-preserve-leading-zeros/ – TsSkTo Nov 28 '13 at 14:28

score 0 · Answer 2 · answered Nov 14 '13 at 12:34

When reading the data, Excel decides it is all numeric and thus decides to convert it to numbers. But to show the data with leading zeros, it would have to be text. Depending on Excel version and file extension (and maybe some other things), Excel directly opens the file, or opens it using a wizard. In my experience, having Excel already open and selecting the file via File/Open increases the chance that the wizard appears. That is what you need.

In the last step of the wizard, select all column headers, and change the radio button setting on top from "Standard" to "Text".

How can you make Excel 2007 stop formatting cells?

2 Answers2