How to☝️ Remove Apostrophe in Excel

Getting rid of leading hidden apostrophes in Excel is a common task.

Leading apostrophes before text aren’t treated as part of your actual values, so using the Find and Replace tool won’t help resolve the issue.

Having to tackle a couple of those bad boys is not a big deal manually, but what if your worksheet is riddled with them?

Stick around to learn four quick-and-dirty ways to remove apostrophes in Excel.

Method #1: Copy & Paste Your Data as Values

Here’s the fastest way to get rid of those annoying leading apostrophes for those short on time.

1. Select your data and copy the values by pressing Ctrl + C.

copy the values

2. Righ-click on the adjacent empty cell.

4. in the contextual menu that appears, under “Paste Options,” select “Values (V)”.

paste values

Congrats! You’ve removed the leading apostrophe from the numbers.

the result without apostrophe

Method #2: Clear Formats

This method allows you to achieve the same outcome without shifting your values around.

1. Select the range that you want to remove the leading apostrophe.

2. Click the Home tab.

3. In the “Editing” group, click “Clear.”

4. Choose “Clear Formats.

clear formats

As a result, your data has been purged from leading apostrophes.

the result without apostrophe

Method #3: Text to Columns

Another great way to work around the issue is to use the Text to Columns feature. Excel will treat your leading apostrophes as delimiters, effectively purging your data from them.

1. Select your cell data.

2. Go to the Data tab.

3. Hit the “Text to Columns” button.

change text to columns

3. The “Convert Text to Columns Wizard” will appear. Make sure that the “Delimited” radio button is selected and click “Next.

select delimited radio button

5. In the “Text qualifier” field, select the apostrophe symbol.

6. Click “Next” to proceed to the final step.

apostrophe in the text qualifier

7. Hit the “Finish” button.

hit the finish button

That’s all! All of the leading apostrophes have been removed from the selected cells.

without apostrophe

Method #4: Multiply Your Cell Values by 1

Finally, you can simply multiply all of your values by 1 to remove apostrophes before text or numbers in Excel.

1. Enter “1” into any empty cell and hit the Ctrl + C shortcut to copy it.

enter number one and copy

2. Highlight the range of cells that you want to remove apostrophes from and right-click on it to open a contextual menu.

3. Click “Paste Special…

click paste special

4. Under “Operation,” select the “Multiply” radio button.

5. Hit “OK.

multiply operation

After you have followed the steps outlined above, Excel will crunch the numbers – you’ll get the same values minus the annoying leading apostrophes.

values minus apostrophe

So these are the four most effective methods you can use to remove leading apostrophes in Excel.

More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X