How to Remove Apostrophe in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Data Set to Remove Apostrophe in Excel

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.

Sample Data Set

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

Opening Find and Replace dialog box

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

Showing Result by Using Find and Replace Command to Remove Apostrophe in Excel

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.

Sample Data Set

  • Then, right-click your mouse.
  • After that, select Copy from the context menu.

Copying Values

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

Using Paste Special Feature to Remove Apostrophe in Excel

  • Now you will spot that all apostrophes are removed.

Showing Result by Using Paste Special Feature to Remove Apostrophe in Excel

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.

Sample Data Set Applying Text to Columns Command

  • After that, a dialog box will appear.
  • Check if the delimited radio button is selected by default.
  • So, just press Finish now.

Opening Convert Text to Columns Wizard

  • Now see, we are done with the operation.

Showing Result by Applying Text to Columns Command to Remove Apostrophe in Excel


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.

Inserting VALUE Function to Remove Apostrophe in Excel

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

Using Fill Handle tool

  • Finally, you will not see any apostrophes in the price column.

Showing Result by Inserting VALUE Function to Remove Apostrophe in Excel


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.

Sample Data Set

  • Secondly, Press  ALT+F11  to open up the VBA editor.
  • Thirdly, go to Insert ▶ Module.

Opening VBA Window

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

Embedding VBA Code to Remove Apostrophe in Excel

  • Now see that we have removed all apostrophes using VBA.

Showing Result by Embedding VBA Code to Remove Apostrophe in Excel


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo