How to get Excel to ignore apostrophe in beginning of cell

Question

I'm writing a tool that syncs a simple database with Excel sheets. Each item in a table in the database corresponds to one row in the worksheet. I read the Excel sheet into the tool using C# and the Excel interop com interface, then compared the items' values (i.e. one of the columns in the excel sheet) after the sync just to make sure that they are equal.

Yesterday I found a case where the comparison wasn't true:

"'<MedalTitle>' Medal - <MedalDescription>"
"<MedalTitle>' Medal - <MedalDescription>"

The second is the one I've read in from Excel, and as you can see it's skipped the first apostrophe. Is there a way to tell Excel to treat the cell as just text (no, just setting the cell's formatting doesn't help)?

I even tried to copy the value ( 'hello' ) of a cell in VBA like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Target.Offset(1, 0).Value = Target.Worksheet.Range("b2").Value
   Target.Offset(2, 0).Value = Target.Worksheet.Range("b2").Formula
   Target.Offset(3, 0).Formula = Target.Worksheet.Range("b2").Formula
   Target.Offset(4, 0).Formula = Target.Worksheet.Range("b2").Value
End Sub

The result was that the value of target cell is always hello'

If there is no way, I'll have to do something ugly like

if (dbitem.value[0] == ''' )
{
   // stuff
}
else
{
   // regular comparison
}

Answer 1

I'm afraid the apostrophe ' is a special character for Excel when it appears as the first character in a cell as you've found. It tells Excel to treat the rest of the string as text, so that you can enter something like '34.2 in the cell, and it'll treat it as the string instead of the number (for formatting and so on).

I suggest doing something similar to what you've suggested, except that where you're putting it into Excel, check the first character, and add an extra ' if there's one there already.

Alternatively, you could prepend an apostrophe to all values - if you want them all as text that is. That way you don't need the extra first character check.