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)



  1. I had been having this issue with the Apostrophe appearing before dates. When I copied the dates to a new workbook and pasted as "Values" - the apostrophe would disappear. Then I would format the original workbook, and paste the "values" back to their original place, and the Apostrophe would reappear! so frustrating. Then I tried the =Value formula to convert the dates without the apostrophe, and when I pasted back to the original column, the Dates were formatted correctly,and the Apostrophe did not re-appear!

    so Simple, but more effective than anything else I have tried.. thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.