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