5 Ways To Remove Apostrophe In Excel Numbers

Many users encounter a frustrating issue when working with numbers in Excel: the presence of an apostrophe (') before the number. This apostrophe can cause problems with formatting, calculations, and data analysis. Fortunately, there are several ways to remove an apostrophe from numbers in Excel.

Understanding the Apostrophe in Excel Numbers

When you enter a number with an apostrophe in Excel, it treats the number as text rather than a numerical value. This can lead to issues with calculations, formatting, and data analysis. The apostrophe is often invisible, making it difficult to detect. However, there are several methods to remove the apostrophe and convert the text to a numerical value.

Method 1: Using the Find and Replace Function

One of the simplest ways to remove an apostrophe from numbers in Excel is by using the Find and Replace function.

Select the range of cells containing the numbers with apostrophes.
Press Ctrl + H to open the Find and Replace dialog box.
In the "Find what" field, type an apostrophe (').
Leave the "Replace with" field blank.
Click "Replace All" to remove the apostrophes from the selected cells.

Method 2: Using the Text to Columns Function

Another way to remove apostrophes from numbers in Excel is by using the Text to Columns function.

Select the range of cells containing the numbers with apostrophes.
Go to the "Data" tab in the ribbon.
Click on "Text to Columns" in the "Data Tools" group.
In the "Text to Columns" dialog box, select "Delimited Text" and click "Next".
Uncheck the "Apostrophe" checkbox in the "Delimiters" section.
Click "Finish" to remove the apostrophes from the selected cells.

Using Text to Columns to remove apostrophe

Method 3: Using the VALUE Function

You can also use the VALUE function in Excel to remove apostrophes from numbers.

Select the cell where you want to display the number without the apostrophe.
Type "=VALUE(A1)", where A1 is the cell containing the number with an apostrophe.
Press Enter to remove the apostrophe and display the number as a numerical value.

Method 4: Using the CLEAN Function

The CLEAN function in Excel can also be used to remove apostrophes from numbers.

Select the cell where you want to display the number without the apostrophe.
Type "=CLEAN(A1)", where A1 is the cell containing the number with an apostrophe.
Press Enter to remove the apostrophe and display the number as a numerical value.

Using CLEAN function to remove apostrophe

Method 5: Using VBA Macro

If you need to remove apostrophes from a large range of cells, you can use a VBA macro.

Open the Visual Basic Editor by pressing Alt + F11.
In the "Editor" window, click "Insert" > "Module" to insert a new module.
Paste the following code into the module:

Sub RemoveApostrophes()
  Dim cell As Range
  For Each cell In Selection
    cell.Value = cell.Value
  Next cell
End Sub

Save the module by clicking "File" > "Save" (or press Ctrl + S).
Select the range of cells containing the numbers with apostrophes.
Press Alt + F8 to open the "Macro" dialog box.
Select the "RemoveApostrophes" macro and click "Run".

Gallery of Excel Apostrophe Removal

Excel Apostrophe Removal

Using Find and Replace to remove apostrophe

Using VALUE function to remove apostrophe

In conclusion, removing an apostrophe from numbers in Excel can be achieved using various methods, including the Find and Replace function, Text to Columns function, VALUE function, CLEAN function, and VBA macro. By following these steps, you can easily remove apostrophes from your data and ensure accurate calculations and formatting.