# 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.

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:

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.