How to Remove Hidden Double Quotes in Excel (6 Easy Ways)

Every now and then we import data from external sources in Excel. In doing so, we come across various inserted characters among entries that cause miscalculation problems in Excel. In this article, we discuss one of the most common pre-existing characters among entries, Double Quotes. We use multiple Excel Features, functions such as SUBSTITUTE, INDIRECT, and MID as well as VBA Macro Code to remove hidden Double Quotes.

In a scenario, we have Employee Name and Address in a dataset. There are hidden Double Quotes in the entries however for better representation and understanding we are showing the Double Quotes explicitly.

How to Remove Hidden Double Quotes in Excel: 6 Easy Ways

Method 1: Using Find and Replace

Excel’s Find and Replace feature finds any character you ask to then replaces it with whatever character you command to. Though we keep the Double Quotes explicit for better understanding, it can be used whether Double quotes are hidden in formulas or anywhere in the worksheet.

Step 1: Go to Home Tab > Select Find & Select (in Editing section) > Click on Replace (from the options).

Step 2: The Find and Replace window appears.

In the Find and Replace window,

in Find what command box Insert Quotation Sign (“) and Keep the Replace with command box Empty. Keep other selections Default.

Click on Find All. It’ll show all the entries containing Quotation Signs underneath the Command icons.

Step 3: Click on Replace All.

Step 4: A confirmation window opens up saying it has made replacements. Click OK.

The execution of steps removes all the Double Quotes from the entries. The consequences look like the picture below.

Method 2: Using Text to Column Function

Excel’s Text to Column feature transforms texts with specific delimiters you choose to. In this case, we use Space as a delimiter to replace it with Double Quotes.

Step 1: Select a range of cells (i.e., Name column). Go to Data Tab > Select Text to Column (in Data Tools section).

Step 2: Convert text to Columns Wizards Steps 1 of 3 window appears. Choose Delimited as Choose the file type that best describes your data. Click on Next.

Step 3: In the Convert text to Columns Wizards Steps 2 of 3 window, Checked Space as Delimiters (you can proceed without selecting any delimiter, that will also work). Click on Next.

Here, we Unchecked the command box saying as Treat consecutive delimiters as one because we don’t have any consecutive delimiters. You can make it Checked if your data type needs to.

Step 4: At last, in the Convert text to Columns Wizards Steps 3 of 3 window, Select Column data format as Text. Click on Finish.

Following all the steps removes Double Quotes from all the selected cells similar to the following image.

 Repeat Steps 1 to 4 for Column C (i.e., Address Column) to come up with a similar result to column B (i.e., Name Column).

 You can see all the Double Quotes disappear.

Read More: How to Remove Single Quotes in Excel

Method 3: Using SUBSTITUTE Function

The SUBSTITUTE function substitutes any characters with declared text in its formula. The syntax of the SUBSTITUTE function is

In the formula,

text; declares the cell reference to change.

old_text; is the text or character to replace within the text.

new_text; is the text to replace with.

[instance]; are the instances the formula replaces old_text with new_text.

Step 1: Paste the following formula in any blank cell (i.e., D4).

Here,

B4; is the text.

CHAR(34); is the old_text.

“”; is the new_text.

We keep [instance] as default.

CHAR(34) is the ASCII code for Double Quotation.

Step 2: Press ENTER and Drag the Fill Handle to bring out all the Names without Double Quotes.

Repeat Steps 1 and 2 for Address Column and the consequences will be the same as the following picture.

Method 4: Using INDIRECT Function to Remove Hidden Double Quotes

In this case, if we have Double Quotes in formulas not showing in cells. We can use the INDIRECT function to remove the quotes. The INDIRECT function has a syntax of

Inside the formula,

ref_text; works as cell reference formatted as text.

a1; delivers the logical argument.

Step 1: Type the following formula in any cells (i.e., D4).

 Inside the formula,

B4; is the ref_text.

As we just removing Double Quotes, we don’t need any logical argument(i.e.,a1).

Step 2: Hit ENTER. You’ll get an outcome similar to the image below.

In this method, you have to Insert the formula for each cell. It’s a hideous job. if you have a handful of data, you can use it unless I’ll not encourage you to use it.

Follow Steps1 and 2 for each cell then the result will be the same as the image below.

Method 5: Using MID Function

The MID function fetches characters from the middle of any string. As the Double Quotes are on both sides of any string, we can use the MID function to remove the quotes. The syntax of the MID function is

Here,

text; is the cell reference you want the characters to extract from.

start_position; is the position within the string you start to extract from.

number_of_characters; is the number of characters from the text you fetch.

Step 1: Write the following formula in any blank cell (i.e., D4).

Here,

B4; is the text.

2; is the start_position, as we want the first character quotation to be removed.

8; is the number_of_characters to extract totally depends on the individual strings.

The name “Jane Doe” has a total of 10 characters including the Double Quotes. We start fetching characters from character number 2 and then extract a total of 8 characters removing both Quotes on both sides.

Step 2: Press ENTER. The name appears as Double Quotes fewer entries similar to the image below.

You have to insert different number_of_characters depending on individual character length. Insert the different number_of_characters for each cell pressing ENTER, you’ll get a depiction like the following picture.

Method 6: Using VBA Macro Code to Remove Hidden Double Quotes

VBA Macro Code is a very effective tool to achieve a result with less effort. In this case, we can use a couple of lines of VBA Macro Code to remove Double Quotes from any selection.

Step 1: Select a range of cells you want to remove the Double Quotes from.

Step 2: Press ALT+F11 altogether to open Microsoft Visual Basic. In the Microsoft Visual Basic window, from the Toolbar, Click on Insert > Select Module.

Step 3: In the Module, Paste the following Macro Code.

Sub RemoveDoubleQuotes()
Dim c As Range
For Each c In Selection
c.Value = Replace(c.Value, Chr(34), " ")
Next c
End Sub

Using the code we apply the formula c.Value to the selection of the worksheet to Replace all the Char(34) (i.e., Double Quotes).

Step 4: Press F5 to run the Macro Code. Back to the worksheet, you’ll see all the double quotes from the entries are removed.

Dataset for Download

Conclusion

In the article, we use Excel’s features, functions, and VBA Macro Code to remove Hidden Double Quotes. For better representation and understanding, we showed the Double Quotes and applied the methods which can remove both explicit and implicit representations. Find and Replace and Text to Column features are handy to work with. The INDIRECT and MID function works for a cell at a time not allowing auto removal. However, the SUBSTITUTE function removes Double Quotes with ease. And the most effective of them all is the VBA Macro code. Hope above discussed methods satisfy your quest. Comment, if you have further queries and have something to add.

Related Articles

• How to Add Single Quotes in Excel
• How to Add Single Quotes and Comma in Excel Formula
• How to Add Single Quotes in Excel for Numbers
• How to Concatenate Single Quotes in Excel
• How to Add Double Quotes in Excel
• How to Add Double Quotes and Comma in Excel with CONCATENATE
• How to Add Double Quotes in Excel Concatenate

<< Go Back to Quotes in Excel | Concatenate Excel | Learn Excel