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.
2. Righ-click on the adjacent empty cell.
4. in the contextual menu that appears, under “Paste Options,” select “Values (V)”.
Congrats! You’ve removed the leading apostrophe from the numbers.
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.”
As a result, your data has been purged from leading apostrophes.
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.
3. The “Convert Text to Columns Wizard” will appear. Make sure that the “Delimited” radio button is selected and click “Next.”
5. In the “Text qualifier” field, select the apostrophe symbol.
6. Click “Next” to proceed to the final step.
7. Hit the “Finish” button.
That’s all! All of the leading apostrophes have been removed from the selected cells.
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.
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…”
4. Under “Operation,” select the “Multiply” radio button.
5. Hit “OK.”
After you have followed the steps outlined above, Excel will crunch the numbers – you’ll get the same values minus the annoying leading apostrophes.
So these are the four most effective methods you can use to remove leading apostrophes in Excel.
