I'm trying to import into a database some data sent to us in Excel, which I rarely use. A single quotation mark is coming across as part of the data of each cell that contains a text value:


When I click on the cell in Excel, the editbox at the top of the spreadsheet, to the right of the {X, check, Fx} buttons, also shows a leading single quote there too. So the leading quotation mark is not being introduced by our import utility. It is in the Excel data.

It's possible this artefact was created on their end outside of Excel, but if you've seen this before and know it's from something that they're doing in Excel, I'd like to know what that is, so I can ask them to stop doing it. Cleaning this data up is really turning into a big time-waster, since there are many of these sheets and we'll be getting many more.

  • How are you importing the data into the database? What is the collation set in the database? Typically excel imports to databases need careful setting of collation. Are you using non-English language (again collation). If not, then it might be that data is entered that way. – Atif Shaikh Sep 3 '14 at 16:29
  • 1
    A leading apostrophe is the usual way to tell Excel that this cell should be treated as text (regardless if a number or formula is in it). It's not an artifact. They inserted it on purpose – nixda Sep 3 '14 at 16:48
  • LOL. They've used the single-quote where Excel would never try to convert to a number (with names) but they didn't use it with cost-center codes that have leading zeros, e.g. 0100, where Excel will change the value. – mrblint Sep 3 '14 at 18:16

The apostrophe ' is a special character for Excel when it appears as the first character in a cell. It tells Excel to treat the rest of the string as text.

It can be removed by:

  • Selecting the cells
  • Then menu Data/ Text-to-columns

You can probably write a macro to automate this.

Alternatively export the data to csv and then import the csv file into your database.

  • Thanks, but an Arrrgh! here for Excel. Data/Text-to-columns must be done column by column, one column at a time. – mrblint Sep 3 '14 at 18:18
  • CSV will probably take the least time. Thanks – mrblint Sep 3 '14 at 18:22
  • Best answer on the internet! So much fancy and convoluted suggestions everywhere. – James Apr 15 '20 at 18:03

Whoa, they changed something in Excel 2013

Microsoft's suggested way to remove hidden apostrophes doesn't work anymore in Excel 2013. I'm pretty sure it worked in previous version.

However, here is a workaround using a temporary variable

Sub removePrefix()
    For Each c In UsedRange
        If c.PrefixCharacter <> vbNullString Then
            temp = c.Text
            c.Value = temp
        End If
End Sub
  • the link is dead now :( – phuclv May 31 '18 at 6:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.