Implicit intersection operator: impact on opening csv files

[beermatt]

Hi,

I have a similar question to this, but regarding impact on opening CSV files in Excel.

If I have a csv file where one of the fields contains a domain prefixed by the '@' symbol (e.g. field contains "@foo.com", "@bar.com", "@hotmail.com") opened into excel this appears as a formula ("=@foo.com", "=@bar.com", "=@hotmail.com"), and appears as "#NAME?" as a result.

Worse, if the file is resaved all these entries are saved as the text "#NAME?", causing loss of the former entries.

Why would it be be expected for Excel to interpret cells starting with '@' (but no '=') as a formula (by adding a non-existent '=' before the entry), particularly for CSV files (a text-based format)? This is a change from previous behaviour/functionality and was unexpected (and undesirable).

[#2]

@beermatt 

That only happens if you open the file with double-click.

Every other way works fine or gives the option to set the data type to "text".

[#3]

@beermatt I always suggest importing data from CSV files in Excel rather than opening it directly with excel. That would be from Data>From Text/CSV.

[#4]

@Detlef Lewinthanks, however I find that if I double-click the file, click File -> Open and browse, or File -> Open and select from recent file list the file just opens (and is interpreted).

@Takmilthanks, unfortunately that doesn't seem to allow (easy) saving after making amendments.

Fundamentally, making simple updates to a csv file used to be simple/straightforward using Excel; if this change in functionality is not a bug (and is working as-expected/as-designed) then I'll need to look into an alternative.

[#5]

@beermatt 

Change the file type to .txt. That invokes the text import assistant when opening the file.

[#6 · Accepted solution]

@beermatt 

I'd consider that as a bug, better to send a frown.

[#7]

@Detlef Lewin: thanks for the suggestion, however that also turns what was previously a simple route to edit a csv file into a multi-step process (from open file, make changes, save & close file to rename file, open excel, start wizard, go through steps of wizard, make changes, save & close, rename file back).

@Sergei Baklan: thanks, now done that; in my opinion it is a bug, will see if that has any effect.

In the interim I've now created a macro in my personal macro workbook to mitigate:-

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Wb.Name Like "*.csv" Then
Cells.Replace What:="=@", Replacement:="'@", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
End If
End Sub

Not an ideal solution (especially if I need others to make amendments to these files) but will probably suffice.