How to Remove Single Quotes in Excel (6 Ways)

Consider a dataset of product descriptions for some products. The texts in the product description column lie between single quotation marks. If you look at a cell, you will notice that there is only a trailing apostrophe. The leading apostrophe of the single quotes is hidden. If you look into the formula bar you’ll be able to see the leading apostrophe. We will remove these single quotes from our dataset.

How to Remove Single Quotes in Excel: 6 Easy Ways

Method 1 – Remove Single Quotes with Find and Replace

Select the cells from where you want to remove the single quotes.
Go to Home, then Editing.
Choose Find & Select, then Replace.

A window named Find and Replace will be opened. In the Find what box, insert a single apostrophe.
Click on Replace All.

A Microsoft Excel confirmation box will appear showing the number of replacements. Click on OK and close the Find and Replace window.

The cells no longer contain the trailing apostrophe of the quotation marks but the leading apostrophe still is in there.

Select the cells and copy them by pressing Ctrl + C.
Select an empty cell and right click on it.
Click on the Paste Special from the menu.

The Paste Special window will appear. Select the Values box and click on OK.

Your data will be copied to a new destination.
If you select a cell and look at the formula bar you will see there is no leading apostrophe. This has removed the single quotes from your dataset.

Read More: How to Add Single Quotes in Excel

Method 2 – The SUBSTITUTE Function to Remove Single Quotes

Insert the following formula in an empty cell D6,

=SUBSTITUTE(C6," ' "," ")

The formula will remove the apostrophes of the single quotes from cell C6.

Press Enter.

Drag the cell D6 down to apply the same formula in all other cells.

Method 3 – REPLACE and LEN Functions

Insert the following formula in an empty cell (D6),

=REPLACE(C6,LEN(C6),LEN(C6),"")

Here, the LEN function will give the length of the string of cell C6, and the REPLACE function will replace the string of cell C6 with a string without quotes and return the resultant string in cell D6.

Press Enter and you will get the string without quotes in cell D6.

Drag the cell D6 down to apply the same formula in all other cells.

Related Content: How to Add Single Quotes in Excel for Numbers

Similar Readings:

Method 4 – The Text to Columns Feature

Select the column from where you want to remove quotations.
Go to Data, then to Data Tools, and click on Text to columns.

A window named Convert Text to Columns Wizard will appear.
Select Delimited and click on Next.

Check Other and insert an apostrophe (‘) in its box.
Click on Next.

Click on Finish.

All the trailing apostrophes of the quotes will be removed.

Select all the cells and copy them by pressing Ctrl + C.
Select an empty cell and right click on it.
Click on the Paste(Values) icon from the menu.

The data will be pasted without the leading apostrophe.

Method 5 – Use the Flash Fill Features to Remove Single Quotes

Manually enter the text without the single quotes of the first cell of the column in an empty cell (D6).
Select a number of cells starting from cell D6 equal to the number of cells of a column in your dataset.
Go to Data, then to Data Tools, and select Flash Fill.

You will get all the data without the single quotes in your selected cells.

Method 6 – Combining LEFT and LEN Functions

Insert the following formula in an empty cell (D6),

=LEFT(C6,LEN(C6)-1)

The LEFT function will return a string from the left side of the cell without considering the leading apostrophe. LEN(C6)-1 portion indicates that the trailing apostrophe will be removed from the returned string.