13

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.

Improve this question
3
  • 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
    Commented Sep 3, 2014 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
    Commented Sep 3, 2014 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
    Commented Sep 3, 2014 at 18:16

2 Answers 2

Reset to default
11

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.

Improve this answer
3
  • Thanks, but an Arrrgh! here for Excel. Data/Text-to-columns must be done column by column, one column at a time.
    – mrblint
    Commented Sep 3, 2014 at 18:18
  • CSV will probably take the least time. Thanks
    – mrblint
    Commented Sep 3, 2014 at 18:22
  • Best answer on the internet! So much fancy and convoluted suggestions everywhere.
    – James
    Commented Apr 15, 2020 at 18:03
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
Improve this answer
1
  • the link is dead now :(
    – phuclv
    Commented May 31, 2018 at 6:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .