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.

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.

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

• Finally, we obtain the texts without apostrophes.

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.

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

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

📕 Read More: 4 Ways to Remove Last 3 Characters 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.

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

• Therefore, the column of numbers presents without apostrophes.

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.

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

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

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.

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

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

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

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.

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

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.

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

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

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

Code

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

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

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

Conclusion

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

• 3 Ways to Remove Non-Numeric Characters from Cells in Excel
• 5 Ways to Remove Last Character from String in Excel
• 3 Ways to Remove Non-Alphanumeric Characters in Excel