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.

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: