How to Remove Apostrophe in Excel (5 Easy Methods)

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

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

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

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

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

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


Similar Readings:


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

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.

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

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

Md. Sourov Hossain Mithun

Md. Sourov Hossain 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo