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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top