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.
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:
- Select a range for replacing the apostrophes. Here, the range is C2:C10.
- Press CTRL+H. It will open the Find and Replace window.
- In Find what, input an apostrophe and leave the Replace with empty.
- Finally, hit the Replace All button. So, it will remove the apostrophe from the selected cells.
- 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.
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.
=SUBSTITUTE(text, old_text, new_text)
- 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:
- First, copy this formula: =SUBSTITUTE(A2,”‘”, “”)
- Paste it in cell C2.
- Hit ENTER.
- 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.
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:
- Go to the sheet’s name and right-click on that. Then, choose View Code and it will open Visual Basic Editor.
- Click on Insert > Module.
- 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
- Click ALT+F8 and this will open the Macros dialog box.
- Click on Run.
- 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.
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:
- First, select the cell data.
- Then go to Data > Data Tools > Text to Columns.
- After clicking, you will see the Convert Text to Columns Wizard window. Select Delimited. Then, click Next.
- 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.
- 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.
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.
Follow these steps:
- Copy the formula: =B2*1
- Paste it into cell C2.
- Press ENTER.
- 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.
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:
- First, click on a blank cell and copy it. You can format the blank cell as per your need.
- Select cells C2 to C10 where you want to store the correct form of the messy data.
- Click the Paste dropdown from the Home tab. Now, select the Paste Special.
- Now, select Formats from the Paste option in the Paste Special window and click OK.
You can see there is no remaining apostrophe in the cells.
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:
- Select the cells in which you want to remove an apostrophe.
- Go to the Home tab.
- Choose the Clear icon in the Editing group.
- Click on the dropdown and select Clear Formats. As you see, we’ve removed the apostrophe. However, you will lose the other formatting too. Therefore, you need to format the cells again.
5. Use VBA Code to Delete Apostrophes in Excel
To remove apostrophes in Excel using VBA, follow these steps:
- Right-click on the sheet’s name and select View Code to open the Visual Basic Editor.
- Click Insert > Module.
- 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
- Go to the worksheet and press ALT+F8 to open the Macros window.
- Now, Click Run.
- Select the range of cells in which you want to remove the apostrophes.
- This message will show after clicking OK.
Now, you can see the result. We removed the apostrophes in all cells.
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.