Csv text field being converted to date format

connieruss

New Member
Joined
Sep 12, 2008
Messages
1
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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
Code:
"'" & 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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,018
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top