#AskMe

Bypass Excel CSV formula Conversion with Operator signs with Simple Practise

You might try to open a CSV file and got error with some columns with error “#NAME?“, it is because of the excel formula conversion issue when there is an operator sign (+, -) in front of the value of the cell as following example:

Some solutions may suggest you to put some few spaces before the value such as ”   +Data1″ to preserve the value for excel in the CSV file but for me, manipulate the data in CSV should not be a good solution.

I would suggest the solution to use the right conversion in Excel, tell him what method to use in different column you may have that issue.

Normally:

  1. You open the CSV (*.csv) to your MS Excel (e.g. MS Excel 2016)
  2. You selected the first column to convert (it would not automatic converted if you are not using comma “,” separator but semicolon “;” or other)
  3. On Data menu, select “Text to Columns
  4. Then do it right here as I show in this following pictures

In my example, two columns may have issue: Header2, Header3 but I change column data format only in “Header3” to use “Text” format.

And the result, here is:

That’s simple.

Or do you think is there any other solutions, you can share to everyone? Please drop them in comment.