When you work with Excel, you can come across a spreadsheet where cells contain apostrophes in front of data, that you don’t want.
We are going to deal with this problem in this lesson, but first, let’s look at the example we are going to use.
By default, the numbers in Excel are aligned to the right side and we have them in cells: A1 and A3.
Next, there are numbers with one apostrophe in cells A2 and A6. These apostrophes are not visible inside a worksheet until you start editing these cells. The cells with an apostrophe at the beginning are automatically converted to text values.
The last two cells: A4 and A5, contain double apostrophes.
There are a few ways you can deal with this problem.
If you have an apostrophe in front of a value in a cell, you can remove them using the following steps.
- Select data with all the cells.
- Click Ctrl + H, or navigat to Home >> Editing >> Find & Select >> Replace.
- In Find what insert an apostrophe, and in Replace with don’t insert anything.
- Click Replace All. We got rid of all visible apostrophes. Now we have to deal with the hidden ones.
- Enter the following formula in cell C1: =VALUE(A1) and autofill it to the rest of the cells.
- Select these cells in column C.
- Copy the cells.
- Use right-click and choose Paste as values.
The VALUE function converts text value to a number unless it can’t be converted, then it returns the #VALUE! error.
This is the result we get.
Remove apostrophes with VBA
There are a lot of steps you have to take in order to remove apostrophes from cells and convert these values to numbers.
But almost everything you can do inside Excel you can do quicker using VBA. Here’s the code that will let you get rid of an apostrophe with a single click.
Select cells you want to be affected by the macro and insert this procedure.
For Each cell In Selection
cell.Value = Replace(cell.Value, "'", "")
Run the macro, and you will get the same result as before.