How to Remove Apostrophe in Excel?

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 apostrophes in Excel. Here, we will demonstrate a simple GIF for a better understanding of how to remove apostrophe in Excel.

How to Remove Apostrophe in Excel: 5 Easy Methods

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. 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 at, why 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 the Replace All command.

• Therefore, you will see that all the apostrophes from the product codes are gone.

Read More: How to Remove Asterisk in Excel

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 the 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 Non-Alphanumeric Characters in Excel

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.

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.

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

Method 5: Embedding VBA Code to Remove Apostrophe in Excel

If you like to code, then it is possible to remove apostrophes 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.

Download Practice Workbook

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

Conclusion

I hope all the methods described above will be good enough to remove apostrophe in Excel. Feel free to ask any questions in the comment section, and please give me feedback.

Related Articles

• How to Remove Parentheses in Excel
• How to Remove Semicolon in Excel
• How to Remove Dashes in Excel
• How to Remove Dashes from Phone Number in Excel
• How to Remove Dashes from SSN in Excel
• How to Remove Blank Characters in Excel
• How to Remove Non-Printable Characters in Excel 

<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel