Excel How to make Text Qualifier editable or add a new entry in the existing list

csvimportmicrosoft excelmicrosoft-excel-2013

I have a CSV file which is not properly formatted because one of the columns contains HTML code which use the same character as the Text Qualifier: the double quote.

Example:

"col1","col2","col3"
"01","text","<html><tag attrib="true">"

I decided to use another character as text qualifier, the backtick ` which is never present in my original file.

My data are like this now:

`col1`,`col2`,`col3`
`01`,`text`,`<html><tag attrib="true">`

Now my CSV is totally valid, but in Excel (2013) I cannot just change the Text Qualifier, it allows me only ", ' or {none}

Is there any way to edit this list to add a new text qualifier?

I finally found another way to import it but I am just wondering why the Text Qualifier box is not editable.

Best Answer

I don't think you can modify the qualifiers list. However, you can escape quotes by doubling them. Like so:

"01","text","<html><tag attrib=""true"">"

This will import correctly into Excel:

enter image description here

Even if you cannot manipulate the CSV more than changing the qualifier, you could do a mass find-replace on your existing CSV in notepad to get it to import correctly:

  1. Create the CSV with backtick text qualifiers as you have already done.
  2. Find-Replace all double quotes with double double quotes.
  3. Find-Replace all backticks with double quotes.
  4. Open with Excel.