Md. Sourov Hossain Mithun
There may be unwanted apostrophes when we copy tables or data from Microsoft Word or collect data from a website to an Excel worksheet or maybe for a typing mistake. Sometimes, we can’t notice they’re staying there because the apostrophes stay hidden and can only be observed when we see the cells’ values in the formula bar or when we double-click the cell. So this article will guide you with 5 easy methods to remove apostrophe in excel. Here, we will demonstrate a simple gif for a better understanding of how to remove apostrophes in Excel.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
5 Easy Ways to Remove Apostrophe in Excel
Let’s get introduced to our dataset first. In my dataset, I have placed some products’ codes, their quantities, and prices.
Now, let’s assume two cases. Firstly, we have some apostrophes in the Product Code column. And secondly, we have inserted the quantities with an apostrophe before them to keep them in text format.
Now, let’s see the following methods one by one how we can remove unexpected apostrophes from the dataset efficiently.
Method 1: Using Find and Replace Command to Remove Apostrophe in Excel
First, take a look that there are accidentally placed apostrophes in every product’s codes.
Now we’ll remove those using the Find and Replace tool.
Steps:
- Firstly, select the data range B5:B12.
- Secondly, press Ctrl+H on your keyboard.
- Then, a dialog box will appear.
- Besides, type apostrophe(‘) in the Find what box and keep the Replace with box empty.
- Finally, just press Replace All command.
- Therefore, you will see that all the apostrophes from the product codes are gone.
Read More: How to Remove Parentheses in Excel (4 Easy Ways)
Method 2: Using Paste Special Feature to Remove Apostrophe in Excel
Now I will show how to remove an apostrophe if it is placed before the numeric value (That’s why I have placed apostrophes before every quantity). I will remove them using Paste special option. For this method, I have added a new column rightward to show the output.
Steps:
- To begin with, select the data range C5:C12 using a mouse or any suitable technique.
- Actually, when you put an apostrophe before any number, it will look like this in the Quantity column numbers.
- Then, right-click your mouse.
- After that, select Copy from the context menu.
- Now click cell D5.
- Later, press Ctrl+Alt+V on your keyboard.
- A dialog box named Paste Special will open up.
- Click on the Values radio button from the Paste options.
- Then just press OK.
- Now you will spot that all apostrophes are removed.
Read More: How to Remove Specific Characters in Excel ( 5 Ways)
Method 3: Applying Text to Columns Command to Remove Apostrophe in Excel
Text to Columns Wizard is a very handy tool to remove apostrophes in excel. In this method, we’ll use it.
Steps:
- Firstly, choose the data range C5:C12.
- Then, click as follows: Data > Data Tools > Text to Columns.
- After that, a dialog box will appear.
- Check if the delimited radio button is selected by default.
- So, just press Finish now.
- Now see, we are done with the operation.
Related Content: How to Remove Characters in Excel (6 Methods)
Similar Readings:
- How to Remove Single Quotes in Excel (6 Ways)
- Remove First 3 Characters in Excel (4 Methods)
- How to Remove Character from String in Excel (14 Ways)
- Remove Hidden Double Quotes in Excel (6 Easy Ways)
- How to Remove Non-Alphanumeric Characters in Excel (2 Methods)
Method 4: Inserting VALUE Function to Remove Apostrophe in Excel
We can use a formula to remove apostrophes in excel using the VALUE function. The VALUE function converts a text value that looks like a number to really a number.
Steps:
- Firstly, activate cell D5.
- Secondly, write down the following formula.
=VALUE(C5)
- Thirdly, hit the Enter button to get the result.
- After that, double-click the Fill Handle icon to copy the formula.
- Finally, you will not see any apostrophes in the price column.
Read More: Remove Specific Character from String Excel (5 Methods)
Method 5: Embedding VBA Code to Remove Apostrophe in Excel
If you like to code then it is possible to remove apostrophe in excel using VBA. I’ll show it with very simple VBA codes.
Steps:
- Firstly, select the data range C5:C12.
- Secondly, Press  ALT+F11 to open up the VBA editor.
- Thirdly, go to Insert ▶ Module.
- Write the codes given below.
Sub DeleteApostrophe()
For Each cell In Selection
cell.Value = Replace(cell.Value, "'", "")
Next cell
End Sub
- Then press the Run icon to run the codes.
- Now see that we have removed all apostrophes using VBA.
Read More: VBA to Remove Characters from String in Excel (7 Methods)
Conclusion
I hope all of the methods described above will be good enough to remove apostrophes in excel. Feel free to ask any questions in the comment section and please give me feedback.
