Removing Apostrophes in ExcelThree Simple Methods

  |
Written By:
Adiste Mae

You may have encountered Excel documents where the apostrophe symbol appears in front of text, numbers, or dates.

You may have observed this issue when copying tables or data from MS Word or importing data from a webpage into the Excel worksheet.

The apostrophes may remain hidden, and you may only be aware of their presence when you view the cell contents in the formula bar. 

This article will explain the purpose and potential effects of hidden apostrophes in Excel, as well as show you three effective methods for removing them.

Keep reading to learn more.

The Problem with Hidden Apostrophes in Excel: Why They Should be Removed

Hidden apostrophes can create confusion and frustration because they can cause cells to behave unexpectedly – they can cause cells to not respond to formatting or produce unexpected results when formulas are applied to them.

For instance, in a table of numbers, if the first four numbers have leading apostrophes, it can cause issues with data analysis or calculation.

When you apply a calculation, such as the SUM function, to a column with hidden apostrophes, the result may not be what you expect.

For example, in this case, you would expect the SUM function to give a result of 1670, but the apostrophes cause the SUM function to only consider the last number, 50, resulting in incorrect output.

The Reason for the Appearance of Apostrophes

A leading apostrophe causes Excel to treat the contents of a cell as a text value, regardless of whether it contains a number or date.

There may be various reasons for wanting Excel to treat numbers or dates as text.

For example, if you enter “Jan-01”, Excel will automatically convert it to a date and format it based on your global date format (such as 01/01/2020).

This can be frustrating if you want to keep the text as “Jan-01” instead.

To prevent this from happening and ensure Excel treats the text as a text value, not a date, you need to indicate that it should be considered as such.

One way to indicate that a value should be treated as text is by adding a leading apostrophe.

Sometimes it may be necessary to keep leading zeros in numerical values. For instance, when working with ID numbers such as 00001234.

If the number is not preceded by an apostrophe, Excel will automatically remove any leading zeros when formatting the number.

Removing Leading Apostrophes in Excel: 3 Methods

If the problem is limited to a few cells, you can remove the apostrophes manually by selecting the cells and deleting the apostrophes.

But if you need to work with a whole column of cells containing apostrophes, there are several methods you can use.

Note:

The Find and Replace function may not work in this case as replacing the apostrophe symbol with a blank will not remove the apostrophe.

Removing Apostrophes using the Text-to-Columns Feature in Excel

The Text-to-Columns feature is a widely used method to remove leading apostrophes in Excel.

It is primarily used to split the contents of cells into multiple columns.

For example, it can be used to separate a cell containing a full name into two cells, one with the first name and the other with the last name.

Additionally, during the process of splitting cells, the Text-to-Columns feature automatically converts numeric values to numbers, date values to dates, and remaining values to text.

This feature provides a quick and effective way to convert apostrophe-delimited cells back to their original number or date formats.

Here is the process for using the Text-to-Columns feature:

  1.     Highlight the range of cells you wish to convert (remove apostrophes from).
  2.     Go to the Data tab in the ribbon, and select the “Text to Columns” button.
  3.     The “Convert Text to Columns” wizard will appear. Click the “Finish” button to complete the process. That’s it!

You will observe that all the leading apostrophes from the chosen cells have been removed.

Multiplying Cells by 1: A Method for Removing Leading Apostrophes in Excel

Here’s another simple and effective method to remove leading apostrophes from cells containing numbers.

  1. Type the number 1 in any empty cell in your sheet.
  2. Press Ctrl+C to copy the value.
  3. Highlight the range of cells you wish to convert (remove apostrophes from).
  4. Right-click and select “Paste Special” from the options that appear. This will open the Paste Special dialog box.
  5. In the “Operation” section, choose the “Multiply” option and click OK. This will multiply every cell in the selected range by 1, resulting in the same numeric values, but without the apostrophes.
  6. You can now delete the number 1, as it is no longer needed.

Note:

It’s important to note that this method only works with numeric values that have leading apostrophes. It will not work with dates or text.

Using VBA (Visual Basic for Applications) Code

The third method to remove apostrophes from a column in your sheet is to use the VBA code.

This method is recommended for experienced Excel users who are comfortable with scripting.

Here is the step-by-step process:

  1.     Go to the Developer tab in the ribbon and select Visual Basic.
  2.     Once the VBA window opens, click the “Insert” option in the menu, then select “Module”. This will add a new module in the project explorer pane.
  3.     Double-click the newly inserted module in Project Explorer to open the module code window.
  4.     Type or copy-paste the following six VBA macro code lines into the module window:
Sub RemoveApostrophe()

With Worksheets("Sheet1").Columns(1)

.NumberFormat = "General"

.Value = .Value

End With

End Sub

Depending on your needs, you will need to make two modifications to line 2 of this code.

  • Substitute “Sheet1” with the name of the sheet containing the cells you want to convert.
  • Also, change the number “1” within the Columns() functions to the number of the column you want to change. For example, Column A is 1, Column B is 2, and so on.

After making the above adjustments, you’re ready to run the macro.

When executed, it will instantly remove all apostrophes from all cells in Column 1.

Steps to Run the Macro:

  1.     Click within the code.
  2.     Select the green play/run button on the toolbar or press F5 on your keyboard.
  3.     Close the VBA window.

After running the macro, check your Excel sheet and you will find that all apostrophes have been removed and all cells have been converted to the number format.

It is important to note that if the column contains some date values with apostrophes and some without, this method may not work as intended.

This is because the method will convert the dates without apostrophes to a number, which ultimately defeats the purpose of removing apostrophes.

Conclusion

These are the three methods to remove hidden apostrophes from cells in Excel.

There are other options available, but these three methods are considered the most effective.

I hope this tutorial was helpful.