How to Remove Apostrophe in Excel?

Unwanted apostrophes can creep into Excel worksheets when copying data from Word or a website, or even from a simple typo. These apostrophes are often hidden and can be tricky to spot. They only show up in the formula bar or when you double-click a cell. This article will show you five easy ways to get rid of unwanted apostrophes from your Excel worksheet.

Method 1 – Using Find and Replace Command to Remove Apostrophe in Excel

Steps:

• Select the data range B5:B12.

• Press Ctrl+H.
• In the dialog box, enter apostrophe(‘) in the Find what box and keep the Replace with box empty.
• Click on Replace All.

• It will remove all the apostrophes from the selected range of cells.

Read More: How to Remove Asterisk in Excel

Method 2 – Using Paste Special Feature to Remove Apostrophe in Excel

To remove an apostrophe if it is placed before a numeric value,

Steps:

• Select the data range C5:C12.

Note: When you enter an apostrophe before a number, it will look like the Quantity column in the image below.

• Right-click on the selected data range.
• Select Copy from the context menu.

• Click on cell D5.
• Press Ctrl+Alt+V.
• A dialog box named Paste Special will open up.
• Click on the Values radio button from the Paste options.
• Click OK.

• All apostrophes will be removed from the selected range.

Read More: How to Remove Non-Alphanumeric Characters in Excel

Method 3 – Applying Text to Columns Command to Remove Apostrophe in Excel

Steps:

• Select the data range C5:C12.
• Click as follows: Data > Data Tools > Text to Columns.

• In the dialog box, check if the delimited radio button is selected by default.
• Click Finish.

• All apostrophes will be removed from the selected range.

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

Steps:

• Select cell D5.
• Enter the following formula.

• Press the Enter button to get the result.

• Double-click the Fill Handle icon to copy the formula to the remaining cells in the column.

• The new column will display the numbers without the apostrophes.

Method 5 – Embedding VBA Code to Remove Apostrophe in Excel

Steps:

• Select the data range C5:C12.

• Press ALT+F11  to open up the VBA editor.
• Go to Insert ▶ Module.

• Enter the codes given below.

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

• Press the Run icon to run the codes.

• All apostrophes will be removed from the selected range.

Download Practice Workbook

Related Articles

• How to Remove Parentheses in Excel
• How to Remove Semicolon in Excel
• How to Remove Dashes in Excel
• How to Remove Dashes from Phone Number in Excel
• How to Remove Dashes from SSN in Excel
• How to Remove Non-Printable Characters in Excel 

<< Go Back To Remove Specific Characters in Excel | Excel Remove Characters | Data Cleaning in Excel | Learn Excel