CSV delimit but "DEC1" gets converted to a date Dec-01 but I need it to stay as the 4 characters DEC1

[Will from London]

Hi

I have a comma separated text data source which includes the four character string DEC1 (it's a Complementary Identification Code meaning a German issued put option on equity if anyone is interested). If I open it in Excel and use "text to columns" my "DEC1" gets converted to 1 December 2001. If I then manually change it to 'DEC1 then it is displayed as the four character DEC1. I need it to be DEC1 not a date and I would prefer not to have to alter this each time as there are many lines of data.

The raw text string is of the form:
*,"TEXT","","","","DEC1","TEXT",123.99

I can edit the source before opening it in Excel and I tried changing it to "'DEC1" but when I open it and do text to columns it now displays as the 5 character 'DEC1 and DEC1 <> 'DEC1.

My question is what character can I add into the source so that Excel treats it as DEC1?

Thanks in advance

Will

 

[John_w]

In the Text to Columns Wizard, change the data format of the DEC1 column to Text (instead of General) and it keeps it as DEC1.

 

[Will from London]

In the Text to Columns Wizard, change the data format of the DEC1 column to Text (instead of General) and it keeps it as DEC1.

That does work, thank you, but that is a bit more manual than I would like ideally. If there is some way of changing the source to give the same effect I'd prefer that.

 

[Tetra201]

Maybe *,"TEXT","","","",="DEC1","TEXT",123.99

 

[John_w]

If there is some way of changing the source to give the same effect I'd prefer that.

I don't know if there is. (="DEC1" imports as ="DEC1" for me.)

It depends how you want to get the data into Excel. If you open the text file from Excel (which runs the Text Import Wizard) then it will open the text data in a separate workbook. If you want to import the data into the current workbook then use the From Text legacy wizard. Either way, record a macro whilst doing the steps so that you can easily repeat the process.

 

[Will from London]

Thanks, both, for your replies.

I produce the csv data in another program (not VBA) and that puts the quotation marks around the text and so I can't put the equals outside unfortunately (otherwise your suggestion Tetra201 would work).

To get it into Excel we drag and drop from windows explorer. Thanks for the suggestion regarding the macro but we have a VBA macro on a custom tab in the ribbon that does text to columns and puts the auto filter on and freezes panes etc but this macro is used for many other csv files (i.e. I do not want to change it to do something slightly different for one column in one file we use twice per calendar quarter).

Perhaps what I'm asking isn't possible. Thanks for your input though.

 

[Akuini]

Perhaps what I'm asking isn't possible. Thanks for your input though.

How about a workaround?
Open the file in a text teditor.
Change DEC1 to something unique, say xxDEC1
Then open it in Excel & do the text to column
Then create a macro to replace xxDEC1 to 'DEC1