I am getting an error as #NAME? while opening CSV File in excel 2016

Question

I am facing problem in Excel.

When I am opening my CSV file in Excel 2016 then I am getting #NAME? error

The formula I am using before converting the CSV file is:

=concatenate("+",substitute("A2"," ","+"))

and the output I am getting XLSX file as +ab+c

where A2 has value as ab c

But when I converting the same file into CSV and reopen the file then the output will change and gives as #NAME?

Answer 1

Summary of my exchanges with OP in comments section.

I see basically three solutions (although the third one does not meet the criteria of the OP, but I add for completeness):

1. Do not "open" the CSV file, but import (in an existing or new workbook): Data > Get External Data > From Text, then you go through the import steps (choose your delimiter, which will be , in your case), and then you have to define all columns as TEXT (this is the most important step, see screenshot below). This is a 100% foolproof solution.
2. Instead of using Excel, you could use another CSV viewer (I understood from OP that a colleague needs to inspect the file before sending onward). This might of course not be feasible in larger organizations where one is not allowed to use other tools, or if the colleague is not cooperative.
3. One can modify the formula so the output becomes ="+ab+c". When this is saved in CSV and imported again into Excel, it will just show +ab+c on the screen. But for OP the text in the CSV file must be just +ab+c, so therefore will not work.

Just a final remark, I could not (using Excel 2013 for Windows 10) replicate the behavior as observed by the OP. +ab+c in a CSV file never gave the #NAME? error for me when I opened the file; however, once I clicked in the cell, Excel would not let me ENTER it, unless I added a ' to indicate it was text; but I still could ESC and leave the cell alone).

I did not do any exhaustive testing, but I observed the following :

• if the text starts with = you get the error always (except if followed by a text string in double quotes "...");
• if it starts with + or -, there must be another operator and number also. So +ab+3 gave an error, but +b1+a not.

So maybe the behavior of Excel is different across versions, or any other settings influence this.

Just to illustrate, herewith a screenshot from the Text Import Wizard (which you find under Data > Get External Data > From Text. And in this final step (actually, you still get another step where you have to define where Excel has to put the text, either in a new worksheet, or in an existing worksheet) you have to select all columns (with shift or ctrl) and select Text as the Column data format.