Why would all text columns in an Excel spreadsheet begin with a leading single quotation mark

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.


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.Clear
            c.Value = temp
        End If
    Next
End Sub