Excel 365 problem when opening CSV file containing the @ symbol

[mrc1925]

Hello,

I am using Excel 365 and have an issue that has appeared sometime within the last 30 days with regards to how Excel opens and converts CSV (comma separated values) files.

I normally open several CSV format files every month. When I open the files, Excel parses out the columns automatically. One of the columns has items that begin with the @ symbol. Previously, this was left as a text field when opened in Excel. Now, when I open these files, there is = sign in front of the @ symbol so Excel thinks this is a formula and then returns the error #name?. Data example below:


SERIAL#, MODEL#, TYPE,PART, PART DESCRIPTION

"SERIAL#1","'0123456","AAA","@thing1","THING ONE"
"SERIAL#2","'0987654","BBB","@THING2","THING TWO"

The PART column with @thing1 appears as =@THING1 which returns the error.

It seems there may have been a logic change of some sort with Excel 365 on how it interprets the @ symbol sometime within the last 30 days.

Any thoughts on how or why this is happening and any way to prevent it?

Thank you

 

[Fluff]

It's to do with how Xl handles implicit intersection in conjunction with the new Dynamic Array functions.
Implicit intersection operator: @

Rather than opening the csv files as if they were Xl files, you will need to import them & set any relevant columns as text.

 

[mrc1925]

Thank you for the quick reply.
So this is a change made within the last 30 days with Excel 365?

 

[Fluff]

It's been running a while, but MS have rolled out the new Dynamic Arrays in a piecemeal fashion.
I got them in Nov last year, but it sounds as though you only got them this month.

 

[mrc1925]

Ok, I just wanted to be sure it wasn't something we could change on our end. We send these files to customers so it looks like we will have to remove that character from all files before we send them.

Thanks for your help!

 

[Fluff]

If you open the csv files in notepad you can do a search & replace to replace @ with '@ and then you shouldn't have a problem

 

[mrc1925]

Yes, I am aware I can do that but we have over 500 files so that would be time consuming. Instead, we are going to use UltraEdit so we can do them all in one batch.

 

[Fluff]

Ok, fair enough. Thanks for the feedback

 

[mrc1925]

And thank you for your help!

 

[Fluff]

My pleasure