Table of Contents
The use of quotation marks in Excel can cause a variety of formatting issues. Quotation marks can be used to enclose text, numerical data, and other characters in a cell, and can cause problems when exporting data, performing calculations, and using functions. Removing quotation marks from Excel cells is therefore important to ensure that data is accurately processed. In this article, we will discuss two methods for removing quotation marks from Excel cells: using the Find and Replace function and using the SUBSTITUTE function.
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.
In conclusion, it is easy to remove quotes from cells in Excel using either the Find and Replace feature or the SUBSTITUTE function. Both methods can be used to quickly and efficiently remove quotes from any cell or cell range in Excel. With these methods in mind, you can now remove any quotes from your Excel data with ease.