How to Remove Single Quotes in Excel (6 Ways)

Sometimes we have data with single quotes in our Excel datasheet. If the data have single quotes Excel automatically hides the leading apostrophe of the quotation. To remove these single quotes you need to follow some techniques. In this article, you will get to know 6 ways to remove single quotes in Excel.

Suppose, in our dataset, we have product descriptions of some products. The texts of product description columns lie between single quotation marks. If you look into a cell, you will notice that there is only a trailing apostrophe. It is because 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. Now we will remove these single quotes from our dataset.

dataset


Download Practice Workbook

Feel free to download the workbook from the link below.


6 Ways to Remove Single Quotes in Excel

1. Remove Single Quotes with Find and Replace Command

We can remove single quotes by using the Find and Replace command. First, select the cells from where you want to remove the single quotes. After that, go to Home > Editing > Find & Select > Replace.

find and replace

As a result, a window named Find and Replace will be opened. In the Find what box, insert a single apostrophe. After that click on Replace All.

replace all

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

conformation box

At this time, you will see that the cells don’t contain the trailing apostrophe of the quotation marks but the leading apostrophe still is in there.

Remove Single Quotes

To remove this leading apostrophe of the quotes, select the cells and copy them by pressing CTRL+C. After that, select an empty cell and right click on that. As a result, a dropdown menu will appear, click on the Paste Special from this menu.

copy

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

paste special

As a result, your data will be copied to a new destination. Now, if you select a cell and look at the formula bar you will see there is no leading apostrophe. That means you have removed the single quotes from your dataset.

Remove Single Quotes

Read More: Remove Specific Character from String Excel (5 Methods)


2. SUBSTITUTE Function to Remove Single Quotes

Another easy way to remove single quotes is using the SUBSTITUTE function. First, insert the following formula in an empty cell (D6),

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

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

substitute

After that, press ENTER, and you will get the data without quotes in cell D6.

function

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

Remove Single Quotes

Related Content: Remove Characters after a Specific Character in Excel (4 Tricks)


3. REPLACE and LEN Functions

You can also use the REPLACE function and the LEN function altogether to remove single quotes. First, type 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.

replace

After that, press ENTER, and you will get the string without quotes in cell D6.

Replace function

At last, drag cell D6 to apply the same formula in all other cells.

Remove Single Quotes

Related Content: How to Remove Characters in Excel (6 Methods)


Similar Readings:


4. Text to Columns Feature

Another way to is to use the Text to Columns feature. First, select the column from where you want to remove quotations. After that go to Data > Data Tools > Text to columns.

data tab

At this time, a window named Convert Text to Columns Wizard will appear. Select Delimited and click on Next.

text to column wizard

In the second step, check on the Other and insert an apostrophe () in the box besides Other. Click on Next.

text to column wizard

In the final step, click on Finish.

text to column wizard

As a result, all the trailing apostrophes of the quotes will be removed.

text to column wizard

To remove the leading apostrophe of the quotes, select all the cells and copy them by pressing CTRL+C. After that select an empty cell and right click on it. Now a dropdown menu will appear. Click on the Paste(Values) sign from this menu.

paste

As a result, the data will be pasted. Now if you select a cell from the copied data and look into the formula bar you can see there is no leading apostrophe. That means the single quotes are removed from your data.

Remove Single Quotes

Read More: How to Remove Non-numeric Characters from Cells in Excel


5. Flash Fill Features to Remove Single Quotes

Flash Fill is another interesting feature of Excel which can be used to remove single quotes. But for that, first, you have to manually enter the text without the single quotes of the first cell of the column in an empty cell (D6).

After that, select a number of cells starting from cell D6 equal to the number of cells of a column in your dataset. Now go to Data > Data Tools and select Flash Fill.

flash fill

As a result, you will get all the data without the single quotes in your selected cells.

Remove Single Quotes

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


6. Combination of LEFT AND LEN Functions

You can also remove the single quotes by using the LEFT function and the LEN function.

First, type the following formula in an empty cell (D6),

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

Here, 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.

LEFT

After that, press ENTER, and you will get the string without quotes in cell D6.

Remove Single Quotes

At last, drag cell D6 to remove the quotations from all other cells.

Remove Single Quotes

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


Conclusion

You can remove single quotes in Excel by following any of the above described ways. If you face any type of confusion, please feel free to leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo