Ah, my part numbers are gone

[mrxwantobe]

Hi. I have someone sending me a .csv file of part numbers. Excel has changed the cell values of some cells from a number like 09-0398 to a date. Is there a way for me to get the correct value back or is the problem from the person who created the file. I change the cell format to general and it does change from a date but it's not the actaul part number. Thanks in advance

 

[nbrcrunch]

format the column as text

 

[mrxwantobe]

i tried that but it changes it to a completely different part number. It's like a 9-1765 ends up a 40062 or something close to that. It appears it it taking a number that it thinks is a date and converting it to a date. Now, when i try to change it to text it does change it but not back to what it was.

 

[Andrew Poulsom]

Change the file extension from csv to txt (or make a copy with a txt extension) and import it. Then you can choose the Text format for that column at Step 3 of the Text Import Wizard.

 

[mrxwantobe]

so here is exactly waht is happening. the part number in Excel is a 9-1906. I format the whole column as text and save the file as a .csv. When you open the .csv the part is now a SEPT-06 and it has changed the cell format to custom. Can this be stopped?

 

[Andrew Poulsom]

Did you try what I suggested?

 

[mrxwantobe]

Yes. I saved the Excel file as a .txt file. Then I opened Excel and imported through the wizard. On the third screen I changed that column to Text and finished the import. As a .txt file it is fine but it must be saved as a .csv. If you look at the cell format it is text so you do a Save As .csv. If you now close the file and reopen the cell format is custom and displayed as a date.

 

[mrxwantobe]

More to the story. by doing what you said it actually fixes the problem. If you look at it in a text editor it displays correctly but if you use Excel it looks wrong. At least now it's usuable. Thanks. If you would like to see the files, let me know.

 

[Andrew Poulsom]

The result you want isn't possible with a csv file, unless you parse it line by line with some VBA code. With a csv file Excel bypasses the Text Import Wizard and parses the data as it sees fit. To get the control that the Text Import Wizard gives you the extension needs to be txt.

As an aside, when you save as csv the file contains exactly what you see on the screen (you can check by opening it in Notepad). It's just when you open it in Excel that you may not get what you want. So if the reason that you must save as csv is that some other application requires that format, you shouldn't have a problem.

 

[mrxwantobe]

You are 100% correct. thanks for the fix. All is well and you have solved a year old question for me.