Save me from my excel hell

November 20, 2009 11:51 AM

Another GD excel/csv filter

I have been having issues with formatting in csv files. I save excel files as csv files. Sometimes I have to go back into the csv files and edit the data.

One of the columns has anywhere from 2 to 7 digit numbers in it. I need to make sure all values in that column are 7 digits. I use the text formula to do it, =text(ad2,"0000000"), take the data and transfer it into that column as values.

However, when I save the data and go back into the file, the numbers have reverted to their other forms in that column.

I make sure to change the column to text and save it that way.

It would be really easy to make the changes in the excel file, however, I have to break the excel file into multiple excel files so I would have to make the changes in the excel file and reseparate into their respective files.

I have also tried reverting each file to excel, making the changes and then re-saving them as csv's, but I still seem to be having issues.

Please help!

posted by TheBones to computers & internet (3 comments total)

Sounds like you should be using leading zeros in the cell format.
posted by anti social order at 12:47 PM on November 20, 2009

After you save the CSV file the first time, open it with Notepad. You should see the 7-digit values with leading zeroes are in there. The problem only occurs when re-loading these files back into Excel. The CSV format has no way to indicate whether a value is a number or text, so Excel has to guess based on what it looks like. These values look like numbers, so when re-loading the file, Excel interprets them as numbers and you lose your leading zeroes.

One solution would be not to re-load the files into Excel. If you just need to edit a couple of values, or delete a whole row, it's pretty easy to do that in Notepad. If you need to do more complicated edits like sorting or moving whole blocks around, that's pretty tough to do in Notepad.

Can you get away with adding a non-numeric character to these 7-digit values, so that Excel doesn't think they are numbers when re-loading the file? For example you could add a leading underscore by changing your formula to look like this:

=text(ad2,"\_0000000")

(the backslash is to get the underscore to be taken literally instead of being interpreted as a special formatting character)

This'll work only if whatever you're doing next with these CSV files can stand having an extra character there. You can't use anything like a leading space, or a plus sign, either, because Excel thinks these are OK for numbers. But an underscore or even an asterisk would make it treat these values as text.

On preview: changing the cell format to have leading zeroes won't help either, because cell formats are also not something the CSV file format can retain.
posted by FishBike at 1:15 PM on November 20, 2009

Not sure I fully understand the question, but to build on FishBike's answer: preceding numbers with an apostrophe indicates to Excel you want to treat the number as text. Excel won't display the leading apostrophe so it will look like a number, but it won't behave as one.
posted by losvedir at 2:04 PM on November 20, 2009