Table of Contents
The process of removing quotes in Excel can be achieved through two methods. The first method involves using the Find and Replace function to locate and eliminate the quotes. This can be done by selecting the data range, pressing the “Ctrl + F” keys, and then entering the quotation marks in the “Find what” field and leaving the “Replace with” field blank. The second method involves using the Text to Columns feature, which can be accessed by selecting the data range, clicking on the “Data” tab, and then selecting “Text to Columns.” This method allows for the conversion of the data format, removing any existing quotes in the process. Both methods offer a quick and efficient way to remove quotes in Excel.
Remove Quotes in Excel (2 Methods)
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: