Remove an Apostrophe in Excel using these 6 Methods

If you use Excel, you will likely come across different symbols and signs at one point or another.

And many of them, such as apostrophes, can be useful and meaningful in their own way.

Yet, apostrophes can sometimes tend to pop up in some of the most unexpected (and unwanted) places while you are working in Excel.

When apostrophes become visible in front of the values within a cell, this can be a perplexing experience – yet it can also present the perfect opportunity to explore more about this common issue that many have faced at some point in time.

And, if you need to get rid of leading apostrophes from your Excel worksheets, then you’ve come to the right place.

Contents:

How Apostrophes That Appear Suddenly in Excel Can Cause Problems

After having worked for hours, looking over your work can be an important part of making sure that everything is in place.

But, then you notice something – the dreaded apostrophes in areas of your spreadsheet where you don’t want them to be.

If you’ve ever come across leading apostrophes that suddenly appear in Excel, then you know how troublesome this can be.

When apostrophes seem to be in the wrong place, they could interfere with a formula and potentially mess up the results.

To make matters worse, trying to find solutions to getting rid of these apostrophes to no avail can make things more difficult than they have to be.

Thus, coming up with an effective solution to remove apostrophes in Excel will become important.

Reasons Why Apostrophes May Show Up

When apostrophes pop up in certain places in Excel, there is usually a reason behind why this is happening.

One of the most common reasons for leading apostrophes appearing is Excel is a necessity to format a cell’s content in a certain way.

For example, a leading apostrophe in front of a value within a cell likely means that it is telling Excel to deal with these values in this cell as text.

Some examples of values that Excel may format are things like dates or numbers– although there could be many different types of data which can be formatted.

When this happens, these apostrophes may sometimes be hidden, meaning that while you may not see them in the cell, they may be visible in the formula bar – indicating that they are indeed there.

Although it may not always be clear at first why symbols like apostrophes show up suddenly, there are several ways to fix this issue.

No matter the reason why apostrophes appear where they do, you may just want your sheet to go back to the way that it should be as quickly and painless as possible.

6 Methods You Can Use to Eliminate Leading Apostrophes in Excel

Although apostrophes that appear seemingly out of nowhere can be a nuisance, hope is not all lost.

And, just as with many other issues in Excel, this common issue of leading apostrophes appearing is reversible and can be undone.

There are quite a few good techniques that you can employ to take away unwanted apostrophes from a spreadsheet in Excel.

And, even better, eliminating apostrophes doesn’t have to be a long, drawn out, complicated process – it can actually be a short and easy process to work through.

Having many different solutions to help you work through your problems with Excel can make it much easier to come up with an answer that works for you.

And, with the right tips implemented, even the most stubborn apostrophes are no match.

This tutorial will show you some helpful techniques which you can implement to remove apostrophes from Excel.

Below are 6 quick and simple ways that you can go about removing apostrophes from Excel.

Let’s get started!

1. Eliminate Leading Apostrophes from Numbers Using the Paste as Values Method

The Paste as Values method is as simple as it is quick.

It focuses on using the value paste option to solve the issue of apostrophes.

To put this method to use, follow these three easy steps:

Select and highlight the cells which contain the leading apostrophes.
Right-click and select “Copy” or hold down Ctrl + C on the keyboard to copy the cell data.
Choose and select an empty cell to paste it.
Right click on this cell, and under “Paste Options”, select the Value option:

Once you’ve completed the steps, you should find that the apostrophes have disappeared from in front of the numeric values.

2. Text-to-Columns Method

The Text-to-Columns option is a handy feature which allows you to divide the contents of cell into more than one column.

In a nutshell, this feature can take multiple values within a cell and split them up among different columns.

For example, if you had a three-letter phrase as the contents of a cell, the Text-to-Columns method could be used to separate the words of that cell into different columns.

Also, this feature can be useful for different tasks in Excel, including removing apostrophes.

To use the Text-to-Columns technique to handle this specific problem:

Choose and highlight the cells which contain the apostrophes that you desire to be removed.
Go to “Data” at the top of the page on the ribbon menu, then click on the “Text to Columns” option.
When the Text to Column Wizard box pops up, click “Finish”.

From here, there should be nothing further to do.

Following this method, you should see that all of the leading apostrophes from the cells that you have selected have disappeared.

3. Using the Clear Formats Method

Using the formats feature is among one of the quickest and easiest ways to fix issues involving apostrophes – as this method uses a straight-forward technique that’s short and to the point.

Not only is this method quick, but it involves less movement of cell contents than some other techniques – making it ideal for removing apostrophes from Excel.

To use the method of clearing the format from cells:

Choose and highlight the range of cells that contain the apostrophes which you want to be removed.
Press and select the “Home” tab at the top, in the menu.
Click on “Clear”, then click on and select “Clear Formats”.

Upon completing the above steps, the leading apostrophes should be removed from the cells.

4. Multiply Cell Values Times 1

Using multiplication can be a surprising, yet effective way to get rid of leading apostrophes from cells that contain numeric values (excluding dates).

To see how multiplication can be used to remove unnecessary apostrophes, try this:

Within an empty cell in Excel, type “1”.
Right-click this cell and select the option “Copy” or use the shortcut CTRL + C with the cell selected.
Choose and highlight the cells which contain the apostrophes that you desire to be removed.
Next, right-click, then click on ‘Paste Special”. This should bring up a dialog box.
Look for where it says ‘Operation’, then select ‘Multiply’ and choose “OK”. This outcome will be every numeric value from each cell that you selected being multiplied by 1 – leaving the same numeric values that you started with in those cells without the apostrophes that were once there.
Remove the “1” from the cell from step 1 as you no longer need it.

As a result of multiplying 1 by the content of the cells, the leading apostrophes should disappear from them – giving you values which appear as they should.

5. Implementing VBA Code to Remove Apostrophes

Another way that you can use to get rid of leading apostrophes in Excel is by using VBA code.

VBA code can be an effective method to make apostrophes that appear in columns a thing of the past.

This method may be a be more involved, but if you don’t mind using code, it can be a great way to remove leading apostrophes from cells.

To use VBA to remove apostrophes from Excel, try the following steps:

Go to the menu ribbon, then click on “Visual Basic”.
When the window for VBA comes up, select “Insert”, then select “Module”.
Click twice on the module which was inserted. The module window should now appear.
Copy and paste or manually type the VBA code below into the module code window which should have appeared from the previous step:

Sub RemoveApostrophe()
With Worksheets(“Sheet1”).Columns(1)
.NumberFormat = “General”
.Value = .Value
End With
End Sub

Note: Line number two of the above code may have to be modified depending on your specific needs.

Change where it says “Sheet1” to the name of the Excel worksheet that contains the cells which you desire to remove the apostrophes from or change.
In the Columns function within the (), change the “1” to the number of the column that you want to be changed.

Run this code. Once you run this code, it will go to the column number that was specified and take out the apostrophes.

Running VBA Code may be a bit more extensive than other methods, yet it can still be useful in getting rid of unwanted apostrophes if you need a solution to fix this particular problem.

6. Implementing Kutools to Remove Apostrophes from Numbers

Kutool is a useful tool that many find helpful for doing a variety of tasks in Excel.

And, Kutools has hundreds of features which make tasks in Excel a lot simpler and easier to get done.

Yet, unknown to many is that Kutool can be used to remove things such as apostrophes from numbers in worksheets.

Note: For this method, you will need to have Kutools installed for Excel in order to do this method.

Select the range of cells that contain the leading apostrophes.
On the menu ribbon, click on “Kutools”, then click “Content”. Next, click on “Convert between Text and Number”.
When the dialog box comes up, select “Text to number”, then click “OK” or “Apply” depending on which option is available.

After using this technique, you should notice the apostrophes have been removed from the numeric values of the selected cells.

Removing Apostrophes Should Be Simple

Although apostrophes might sometimes show up where you least expect them to, even they can be tamed.

Go through as many of the methods we mentioned above as needed until you find one that solves the problem.

We hope these methods have solved your issue.