How to Remove Apostrophe in Excel (5 Easy Methods)

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 the 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 apostrophes in excel.


Download Practice Book

You can download the free Excel template from here and practice on your own.


5 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: Find and Replace Tool to Remove Apostrophe in Excel

At 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.

Step 1:

➤ Select the data range B5:B12.

➤ Then press Ctrl+H on your keyboard.

A dialog box will appear.

Find and Replace Tool to Remove Apostrophe in Excel

Step 2:

➤ Now type apostrophe(‘) in the Find what box and keep the Replace with box empty.

Finally, just press Replace All.

Find and Replace Tool to Remove Apostrophe in Excel

Now 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: Use Paste Special to Erase 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.

Step 1:

➤ Select the data range C5:C12 using a mouse or any suitable technique.
➤ Then right-click your mouse.
➤ Select Copy from the context menu.

Paste Special to Erase Apostrophe in Excel

Step 2:

➤ Now click the Cell D5
➤ Later, press Ctrl+Alt+V on your keyboard.

A dialog box named Paste Special will open up.

Paste Special to Erase Apostrophe in Excel

Step 3:

➤ Click on the Values radio button from the Paste options.
➤ Then just press OK

Paste Special to Erase Apostrophe in Excel

Now you will spot that all apostrophes are removed.

Read More: How to Remove Specific Characters in Excel ( 5 Ways)


Method 3: Apply Text to Columns Wizard to Delete Apostrophe in Excel

Text to Columns Wizard is a very handy tool to remove apostrophes in excel. In this method, we’ll use it.

Step 1:

➤ Select the data range C5:C12.
➤ Then click as follows: Data > Data Tools > Text to Columns.

A dialog box will appear.

Text to Columns Wizard to Delete Apostrophe in Excel

Step 2:

➤ Check if the delimited radio button is selected by default.
Just press  Finish now.

Text to Columns Wizard to Delete Apostrophe in Excel

Now see, we are done with the operation.

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


Similar Readings:


Method 4: Insert 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.

Step 1:

➤ Activate Cell D5.
➤ Write the formula given below:

=VALUE(C5)

➤ Hit the Enter button to get the result.

VALUE Function to Remove Apostrophe in Excel

Step 2:

➤ After that, double-click the Fill Handle icon to copy the formula.

VALUE Function to Remove Apostrophe in Excel

Now you will see that all the apostrophes are erased.

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


Method 5: Embed VBA to Delete 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.

Step 1:

➤ Select the data range C5:C12.
Right-click your mouse to the sheet title.
➤ Select View Code from the context menu.

A VBA window will appear.

VBA to Delete Apostrophe in Excel

Step 2:

➤ 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.

VBA to Delete Apostrophe in Excel

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo