There are two common ways to remove quotes from cells in Excel:
Method 1: Use Find and Replace Feature
Method 2: Use SUBSTITUTE Function
The following examples show how to use each method in practice with the following list of basketball team names in Excel:
Let’s jump in!
Example 1: Remove Quotes Using Find and Replace Feature
First, highlight the cell range A2:A11.
Then type Ctrl + H to bring up the Find and Replace feature in Excel:
Then type “ in the Find what box and leave the Replace with box blank.
Once you click Replace All, the quotes in each cell will be removed and a message box will appear that tells you how many replacements were made:
Notice that the quotes in each team name have been removed.
Example 2: Remove Quotes Using SUBSTITUTE Function
Another way to remove quotes from a string in Excel is to use the SUBSTITUTE function.
We can type the following formula into cell B2 to remove the quotes from cell A2:
=SUBSTITUTE(A2, CHAR(34), )
We can then click and drag this formula down to each remaining cell in column B:
Column B returns each team name from the corresponding cell in column A with the quotes removed.
Note that CHAR(34) is the character code for double quotes.
By using the SUBSTITUTE function, we substitute each occurrence of double quotes in the cell with nothing.
This has the same effect as removing all quotes from each cell.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Add Single Quotes to Text
Excel: How to Extract Text Before a Character
Excel: How to Extract Text After a Character
Excel: How to Remove Specific Text from Cells
