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.
How to Remove Single Quotes in Excel: 6 Easy Ways
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.
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.
Now a Microsoft Excel confirmation box will appear showing the number of replacements. Click on OK and close the Find and Replace window.
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.
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.
Now, the Paste Special window will appear. Select the Values box and click on OK.
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.
Read More: How to Add Single Quotes in Excel
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.
After that, press ENTER, and you will get the data without quotes in cell D6.
Drag the cell D6 to apply the same formula in all other cells.
Related Content: How to Add Single Quotes and Comma in Excel Formula
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.
After that, press ENTER, and you will get the string without quotes in cell D6.
At last, drag cell D6 to apply the same formula in all other cells.
Related Content: How to Add Single Quotes in Excel for Numbers
Similar Readings:
- How to Concatenate Single Quotes in Excel
- How to Add Double Quotes in Excel
- How to Add Double Quotes and Comma in Excel with CONCATENATE
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.
At this time, a window named Convert Text to Columns Wizard will appear. Select Delimited and click on Next.
In the second step, check on the Other and insert an apostrophe (‘) in the box besides Other. Click on Next.
In the final step, click on Finish.
As a result, all the trailing apostrophes of the quotes will be removed.
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.
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.
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.
As a result, you will get all the data without the single quotes in your selected cells.
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.
After that, press ENTER, and you will get the string without quotes in cell D6.
At last, drag cell D6 to remove the quotations from all other cells.
Download Practice Workbook
Feel free to download the workbook from the link below.
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
<< Go Back to Quotes in Excel | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
