8

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:

    'PUBLIC, JOHN Q.

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 Rahman 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
5

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
1

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
  • the link is dead now :( – phuclv May 31 '18 at 6:30

protected by Community Feb 13 at 12:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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