Excel 365 problem when opening CSV file containing the @ symbol

mrc1925

New Member
Joined
Jan 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Thank you for the quick reply.
So this is a change made within the last 30 days with Excel 365?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
Ok, fair enough. Thanks for the feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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
Top