Removing Hidden Apostrophes from Imported Numbers in Microsoft Excel 2010

In this article, we ill learn how to remove hidden apostrophes from imported numbers in Microsoft Excel 2010.

While importing data from internet or any software, the numbers include leading apostrophe. The numbers with apostrophe are considered as text in Excel.

These numbers can't be formatted as Number, Currency, Percentage, Date, Time, etc. They are also can't be used in calculations such as SUM or AVERAGE because of their text format. Functions will not work on text format unless they are converted to numbers.

We will use VALUE function to convert text into numbers.

Value: Converts a text string that represents a number to a number

Syntax: =VALUE(text)

Let us take an example how to remove apostrophe from cells:
 

• We have some numbers in column A including apostrophe in front.

• To figure out if the cell contains apostrophe is number by default will always be right aligned while numbers containing apostrophe are left align.
• The second hint we get from the yellow color square box is popping up.

• If you move the cursor over the yellow square box, you will find that the color will get much brighter.

• When you click on the yellow square box, you will find the Convert to Number option

• When you click on Convert to Number, the text will convert it to a number.

• The second way to convert the number containing apostrophe is by using Value function.
• In the above example, enter formula in cell B1 =VALUE(A1)