7 Quick Tricks to Remove Apostrophe in Excel

Sometimes we need to gather data for surveys on various issues. Following that, we must prepare an MS form or Google Form. Based on the question-answer, a spreadsheet or MS Excel file is generated. Sometimes it may happen that numbers or texts are associated with unnecessary apostrophes. In this case, we must remove the apostrophe from the Excel file to work with the data.

Also, when you are likely to process data from software, you will find data associated with many apostrophes. Basically, data is separated with apostrophes. So, to sort out and work with the data, it is inevitable to remove apostrophe from Excel. In this article, we will learn several methods to remove apostrophe from a dataset in Excel.

📁 Download Excel File

To practice please download the excel file from here:

Learn to Remove Apostrophe in Excel with These 7 Ways

In this article, we explore 7 catchy approaches to remove apostrophe in Excel. So following that we considered a data set named Sales Data of Amigo Super Store- 2022 containing Seller, Product Code, Products, Quantity, and Amount. The dataset also has 16 rows and 7 columns. To remove apostrophes from the texts and numbers, we are going to use the Find and Replace feature, Paste Special feature, VALUE function, and VBA Macro. Let’s get started.

Dataset of how to remove apostrophes in Excel.


1. Use Find and Replace Tool to Remove Apostrophe from Text in Excel

The easiest way is to find the apostrophe in a dataset and replace them. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the total data range B5:F13.
  • Then click on the drop-down of the Editing feature from the Home menu of the Top Ribbon.
  • Next, navigate Find and Select option to select the Replace option.

Replacing apostrophes to remove from texts.

  • Thus a box named Find and Replace appears.
  • Then, insert {}= Apostrophe in the Find what cell and { }= blank space in Replace with cell.
  • After that hit the Replace All button.

Find and Replace box.

  • Finally, we obtain the texts without apostrophes.

Apostrophe removed from texts in Excel.


2. Convert into Numbers to Remove Apostrophe in Excel

Suppose we need to remove apostrophes from the numbers. What to do in this case? How can we remove and note down only numbers? Please follow the steps below to remove apostrophes from numbers.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the total data range E6:E13 containing numbers.
  • Secondly, click on the yellow marked error box.

cell values convert into numbers to remove apostrophes.

  • Thirdly, pull down the list from the drop-down box and select the Convert to numbers option.

Context menu to Convert to Number.

  • Therefore, we get the numbers without apostrophes in that selected range.

Numbers converted erasing apostrophes in Excel.

📕 Read More: 4 Ways to Remove Last 3 Characters in Excel


3. Apply Text to Columns Feature to Delete Apostrophe in Excel

Applying the Text to Columns Wizard feature one can easily erase apostrophes before the numbers, Please check out the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select the total data range E6:E13 containing numbers.
  • Then, Select the Text to Columns feature from the Data menu of the Top Ribbon.

Text to Columns wizard to remove apostrophes in Excel.

  • Then, a box named Convert Text to Columns Wizard appears.
  • After that, choose Delimited to separate commas or apostrophes from the numbers.
  • Further click on the Finish to end process.

Convert Text to Columns Wizard box.

  • Therefore, the column of numbers presents without apostrophes.

Apostrophes from the numbers were erased in Excel.


4. Use VALUE Function to Erase Leading Apostrophe in Excel

VALUE function only considers the numbers eliminating commas, apostrophes or hyphens, etc. Using the VALUE function you can easily erase the leading apostrophe of a cell and consider only value. Please check out the following procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select the blank cell i.e. F6.
  • Then, insert the following formula containing the VALUE function in the F6 cell.

Removing apostrophes using multiplication operation in Excel.

  • The VALUE function only takes the value neglecting the apostrophe from the F6 cell.
  • Therefore, by auto-filling the column, we obtain the values without apostrophes.

Output without apostrophes.

📕 Read More: 6 Ways to Remove Blank Characters in Excel


5. Use the Copy-Paste Method to Get Rid of Apostrophe

Copying the numbers, with the help of the Paste Special option one can quickly eliminate apostrophes without any hustle.  Please check out the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the data range E6:E13 containing numbers.
  • Then Right-Click on the mouse to get the Context Menu list.
  • Next, click on Copy from the Context menu list.

Copy-Paste method to delete apostrophes.

  • Now to paste the values, select the output cells range F6:F13.
  • Then, Right Click on the Mouse to get the Context Menu again.
  • Next, select Paste Special from the Context Menu list.

Paste Special option to remove apostrophes.

  • Thus a box named Paste Special appears.
  • After that Select Values from the Paste option.
  • Further, select the multiply from the Operation segment and hit the OK button.

Paste Special box.

  • Therefore, lastly, we obtain the output without apostrophes.

Output without apostrophes.


6. Multiply Cell Values to Eliminate Apostrophe

Multiplying the cell values, you can eliminate apostrophes from the cells. Multiplying the value associated with apostrophes with 1 is enough to get rid of apostrophes. Check out the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, create a column named Multiply.
  • Secondly, insert 1 in every cell in the Multiply column.
  • Thirdly, in G6, multiply E6 and F6 to get the multiplied value.

Multiplication to remove apostrophes from numbers in Excel.

  • In the end, by auto-filling the cells of column G, we achieve the output without apostrophes.

Removing the apostrophe from the numbers in Excel.


7. Use VBA Macro to Remove Apostrophe Before Numbers in Excel

Using VBA code we can easily remove apostrophes before numbers in Excel. With the help of the Developer option, you can generate a program on your own.  Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Check first if your Developer option is available or not.
  • Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

Enabling VBA Developer menu.

  • Thirdly, click on Developer and click on the OK button.

Excel options box to get the Developer menu in the Top Ribbon.

  • Now Developer mode is on.
  • Then, click on the Visual Basic option.
  • Next, to insert the VBA code, click on Module from the Insert menu.

VBA box to remove the apostrophe from numbers in Excel.

  • After that, select the range to remove apostrophes.
  • Further, insert the following code and click on the Run icon.


Sub DeleteApostrophe()
    For Each cell In Selection
        cell.Value = Replace(cell.Value, "'", "")
    Next cell
End Sub

Inserting VBA code to remove the apostrophe from numbers in Excel.

  • Lastly, obtain the result without any apostrophe in the selected range.

Apostrophes are removed from numbers in Excel.

📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Change cell reference according to your worksheet.

🖊️  VALUE function only allows numbers in a selected range.

📝 Takeaway from This Article

📌  VBA Macro to remove apostrophes from a dataset.

📌  VALUE function to extract the values neglecting apostrophe from number.

📌  Find and Replace tool to erase apostrophes from the text.

📌  Text to Column Wizard feature to delete apostrophes from numbers.


We demonstrated every possible way to remove apostrophes from the dataset in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.

Related Articles

(Visited 52 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply