How to Remove Apostrophe in Excel [8 Methods]

If the apostrophe (‘) is situated in the middle or end of the text strings of your dataset, you can use the Find and Replace feature to remove the apostrophe in Excel. Otherwise, if it appears at the start of a cell’s text string, using the Text to Columns tool is the most straightforward approach to removing an apostrophe.

3 Methods to Remove Apostrophe in Excel When Apostrophe is Visible

In this dataset, you can see a column of Product Code. However, it contains apostrophes between the alphabetical and numerical parts. These unexpected apostrophes can through you into trouble. For example, while looking for a particular value or looking for duplicates.

So using the dataset below I will show you how to remove these apostrophes when they are visible. To keep the updated product codes, I’ve taken a column called Updated Product Code.

Case 1: Remove Apostrophes in Excel Using Find and Replace Feature

One approach to solving this issue is to use the Find and Replace dialog box. Find searches for a specified text or value within the worksheet. Then, Replace replaces with the previous value with a new text or value.

To remove apostrophes in Excel using the Find and Replace dialog:

1. Select a range for replacing the apostrophes. Here, the range is C2:C10.
2. Press CTRL+H. It will open the Find and Replace window.
3. In Find what, input an apostrophe and leave the Replace with empty.
4. Finally, hit the Replace All button. So, it will remove the apostrophe from the selected cells.
5. You will see this message after replacement. Just hit OK.

After completing the previous steps, you will see the following output. The result is apostrophe-free and presented in column C.

Case 2: Use the SUBSTITUTE Function to Delete Apostrophes in Excel

You can use the SUBSTITUTE function to replace any specified string within a given string or text with another text or string.

Syntax

=SUBSTITUTE(text, old_text, new_text)

Formula

=SUBSTITUTE(A2,"'", "")

Formula Explanation

• A1: A1 refers to the cell or text string from which you want to remove apostrophes.
• SUBSTITUTE: An Excel function that replaces occurrences of a specified substring with another substring.
• “’“: It specifies that you want to replace the apostrophe.
• “”: Empty string means, it will remove the apostrophe.

To remove apostrophes in Excel use the SUBSTITUTE function:

1. First, copy this formula: =SUBSTITUTE(A2,”‘”, “”)
2. Paste it in cell C2.
3. Hit ENTER.
4. Double-click on the Fill Handle to copy down the formula.

So, I’ve successfully removed the apostrophe from the updated product code. See the screenshot.

Case 3: Remove Apostrophes in Excel by Using VBA Code

You can use VBA to remove the apostrophes in Excel. Here are the steps to follow:

1. Go to the sheet’s name and right-click on that. Then, choose View Code and it will open Visual Basic Editor.
2. Click on Insert > Module.
3. Copy the code below and paste the code.

   Sub RemoveApostrophes()
   
   Dim rng As Range
   Dim cell As Range
   
   ' Prompt the user to select a range
   On Error Resume Next
   Set rng = Application.InputBox("Select a range where you want to remove apostrophes:", Type:=8)
   On Error GoTo 0
   
   ' Check if a range was selected
   If rng Is Nothing Then
   MsgBox "No range selected. Operation canceled.", vbExclamation
   Exit Sub
   End If
   
   ' Loop through each cell in the selected range
   For Each cell In rng
   If InStr(1, cell.Value, "'") > 0 Then
   ' Check if the cell contains an apostrophe
   cell.Value = Replace(cell.Value, "'", "") ' Remove the apostrophe
   End If
   Next cell
   
   End Sub

   

4. Click ALT+F8 and this will open the Macros dialog box.
5. Click on Run.
6. Now, select a range. Click on OK.

Now, see the result. The apostrophes are removed.

5 Methods to Remove Apostrophe in Excel When Apostrophe is Invisible

In the following data, there are details of the product name and delivery date. However, all the dates in the Delivery Date column have leading apostrophes which are invisible. If you select any cell of the Delivery Date column, you will see the leading apostrophe in the formula bar.

Hence, to remove these apostrophes, I have inserted a column beside that called Corrected Date to keep the updated dates.

Case 1: Remove Apostrophe in Excel with Text to Columns Tool

The Text to Columns feature helps you to separate data from a single column into a different column for better organization. Therefore to remove apostrophes, you can use this feature. Here is the step-by-step guide:

1. First, select the cell data.
2. Then go to Data tab > Data Tools group > Text to Columns tool.
3. After clicking, you will see the Convert Text to Columns Wizard window. Select Delimited. Then, click Next.
4. After clicking Next, you will see this interface. Here select Tab in the Delimiters. Then, in the Text qualifier, press an apostrophe or single quotation. After that, click Next again.
5. In this window, you have to select General in the Column data format. Now, click Finish.

When you click Finish, then you will see the result. We have got the corrected date in column C.

Case 2: Multiply the Cell Value with 1 to Remove Apostrophe in Excel

To remove the apostrophe, you can use a simple formula of multiplication. By multiplying with 1, the cell value remains the same and the apostrophes are removed.

Syntax

=Cell Reference*

Formula

=B2*1

Follow these steps:

1. Copy the formula:  =B2*1
2. Paste it into cell C2.
3. Press ENTER.
4. Use the Fill Handle to copy the formula from C2 to C10.

You can see the result after clicking the Fill Handle. We got our correct format of date in column C.

Case 3: Use Copy-Paste to Clear the Invisible Apostrophe in Excel

If you have an apostrophe at the beginning of a cell, for example- cellphone number. Excel sometimes shows you a warning. To remove the apostrophe, you can use the Copy–Paste formula of Excel.
Here the cell contains invisible apostrophes in column B. We will rectify the data and store it in the adjacent column C. I have added the procedures.

Follow these steps to remove the invisible apostrophe in Excel:

1. First, click on a blank cell and copy it.
   You can format the blank cell as per your need.
2. Select cells C2 to C10 to store the correct form of the messy data.
3. Click the Paste dropdown from the Home tab.
4. Now, select the Paste Special.
5. Now, select Formats from the Paste option in the Paste Special window.
6. Click OK.

You can see there is no remaining apostrophe in the cells.

Case 4: Remove Apostrophes in Excel Using Clear Formatting of Cells

Clear Formats is a quick way to reset the default formatting of any cell. You can use this also to remove the apostrophes in Excel. Follow this guide:

1. Select the cells in which you want to remove an apostrophe.
2. Go to the Home tab.
3. Choose the Clear icon in the Editing group.
4. Click on the dropdown and select Clear Formats.

As you see, we’ve removed the apostrophes. However, you will lose the other formatting too. Therefore, you need to format the cells again.

Case 5: Use VBA Code to Delete Apostrophes in Excel

To remove apostrophes in Excel using VBA, follow these steps:

1. Right-click on the sheet’s name and select View Code to open the Visual Basic Editor.
2. Click Insert > Module.
3. Copy the code below and paste it into the newly created module.

   Sub RemoveInvisibleApostrophes()
   
   Dim rng As Range
   Dim cell As Range
   Dim cellValue As String
   Dim newValue As String
   Dim i As Integer
   
   ' Prompt the user to select a range
   On Error Resume Next
   Set rng = Application.InputBox("Select a range of cells:", Type:=8)
   On Error GoTo 0
   
   ' Check if a range was selected
   If rng Is Nothing Then
   MsgBox "No range selected. Operation canceled.", vbExclamation
   Exit Sub
   End If
   
   ' Loop through each cell in the selected range
   For Each cell In rng
   ' Get the cell's value as a string
   cellValue = CStr(cell.Value)
   newValue = ""
   
   ' Loop through each character in the cell's value
   For i = 1 To Len(cellValue)
   ' Check if the character's ASCII code is 146 (invisible apostrophe)
   If Asc(Mid(cellValue, i, 1)) <> 146 Then
   ' Append non-apostrophe characters to the newValue
   newValue = newValue & Mid(cellValue, i, 1)
   End If
   Next i
   
   ' Update the cell's value with the newValue
   cell.Value = newValue
   Next cell
   MsgBox "Invisible apostrophes removed from the selected range.", vbInformation
   
   End Sub

4. Go to the worksheet and press ALT+F8 to open the Macros window.
5. Now, Click Run.
6. Select the range of cells in which you want to remove the apostrophes.
7. This message will show after clicking OK.

Now, you can see the result. We removed the apostrophes in all cells.

Conclusion

Excel provides many features and custom VBA code to help you clean and format your spreadsheet effectively. You can use them to get rid of visible apostrophes as well as leading or invisible apostrophes, from your data. In this article, I have gathered all possible ways to remove apostrophes in Excel.

Frequently Asked Questions

How do I remove special characters in Excel?

To remove special characters in Excel, use the SUBSTITUTE function. Here’s a concise formula: =SUBSTITUTE(cell_reference, “special_characters”, “”)
Replace “cell_reference” with the reference to the cell containing the text, and “special_characters” with a list of characters you want to remove. This formula substitutes specified special characters with an empty string, effectively removing them from the text. Adjust the cell reference and special characters as needed for your specific case.

How do I remove commas in Excel?

To remove commas in Excel, use the SUBSTITUTE function. Here’s a concise formula: =SUBSTITUTE(cell_reference, “,”, “”)
Replace “cell_reference” with the reference to the cell containing the text. This formula substitutes commas with an empty string, effectively removing them from the specified cell in Excel. Adjust the cell reference as needed for your specific case.

How do I remove special characters from a string?

To remove special characters from a string in Excel, use the SUBSTITUTE function. Here’s a concise formula: =SUBSTITUTE(cell_reference, CHAR(1),””)
Replace “cell_reference” with the reference to the cell containing the text. This formula substitutes any special characters, represented by CHAR(1), with an empty string, effectively removing them from the specified cell in Excel. Adjust the cell reference as needed for your specific case.