Csv text field being converted to date format

[connieruss]

Hope someone can help. I have a Iseries pgm that allows our customer service to generate and e-mail a costomized price list of some 25000 items. We create a csv file and attach and e-mail.
The problem is the part field is a 6 digit field including a dash. a great many come out correctly but it comes across some it automatically converts ie 09-5001 becomes "Sep-01" but then a couple come out like this and then a 09-1098 comes fine.
If i go to that cell and try to format to text or number I get 1132862 , nothing at all like it? Help please.

 

[PCL]

Date in EXCEL are numbers counting the number of day since January 1900.
If you format the cell with contain = 1132862 as date you get September 1, 5001.
Why not to add to the part field a character to turn it as text such as #09-5001. ( It can be removed using formula =RIGHT(B4,LEN(B4)-1))

 

[Cindy Ellis]

Hello connieruss, and Welcome to the board!
This is a case of Excel being too smart for its own good.
For manual solution...
Instead of "double-clicking" the file to open it, use File Open from within Excel. Select the file (change "Files of Type" as necessary to locate the file), then in the text import wizard choose "Delimited", click "Next", choose "comma" (or whatever the delimiter is), and...here's the important part...click "Next" again. Highlight the column with part numbers, and choose the data type as "Text". The next key step is to simplify things for your customer so that they don't face the same issue. One way to address this (similar to what PCL suggested, but with no apparent change to the part number value) would be to insert an apostrophe at the beginning of each part number. This can be done to all part numbers in just a couple of steps. In a blank column, enter the formula

"'" & B1

(subsituting the appropriate cell reference to your part numbers). Copy this formula all the way down (double-clicking the lower right corner should work). Highlight the new column, and Copy, Paste-Special, Values to replace the formula with the new string. It looks just like the original part numbers, but the leading apostrophe will force them to be interpreted as text.
If you need an automated solution, this could be put into a macro...just post back.
Hope this helps,
Cindy