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.

Dataset-How to Remove Hidden Double Quotes in Excel

Dataset for Download

6 Easy Ways to Remove Hidden Double Quotes in Excel

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

find and replace -How to Remove Hidden Double Quotes in Excel

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.

Find and Replace window

Step 3: Click on Replace All.

Commands

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

Confirmation window

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

Find and Replace final result

Read More: How to Remove Characters in Excel (6 Methods)

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

text to column-How to Remove Hidden Double Quotes in Excel

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.

Convert text to column wizard

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.

convert text to column wizard

 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.

convert text to column wizard

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

text to column result

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

text to column final result-How to Remove Hidden Double Quotes in Excel

 You can see all the Double Quotes disappear.

Read More: How to Remove Single Quotes in Excel (6 Ways)

Method 3: Using SUBSTITUTE Function

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

SUBSTITUTE (text, old_text, new_text, [instance])

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

=SUBSTITUTE(B4,CHAR(34),"")

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.

substitute function -How to Remove Hidden Double Quotes in Excel

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

Drag the fill handle

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

Substitute function final result-How to Remove Hidden Double Quotes in Excel

Related Content: How to Remove Spaces in Excel: With Formula, VBA & Power Query


Similar Readings:


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

INDIRECT(ref_text, [a1])

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

=INDIRECT("B4")

 Inside the formula,

B4; is the ref_text.

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

indirect function-How to Remove Hidden Double Quotes in Excel

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

Indirect formula

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.

indirect function final result-How to Remove Hidden Double Quotes in Excel

Read More: How to Remove Special Characters in Excel (4 Methods)

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

MID( text, start_position, number_of_characters )

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

=MID(B4,2,8)

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.

mid function-How to Remove Hidden Double Quotes in Excel

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.

MID formula

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.

mid function final result-How to Remove Hidden Double Quotes in Excel

Related Content: Remove Last Character from String Excel (5 Easy Methods)

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.

vba macro code -How to Remove Hidden Double Quotes in Excel

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.

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

Macro code

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.

vba macro code final result-How to Remove Hidden Double Quotes in Excel

Read More: VBA to Remove Characters from String in Excel (7 Methods)


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo