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 into the tool using C# and the Excel interop com interface, and compare 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

try targetcell.Value instead. .Formula is the formula seen in the formula bar while .Value is the evaluated value of the cell.

So, I am guessing that you would have used .Formula in your original code as well. Changing that should work.

EDIT: Ok, it did not work (embarrassed).

Excel treats the starting single quote specially.. so specially that even obscure cell / range properties do not have access. The only workaround I could find is essentially the same as what you thought initially. Here goes:

If VarType(cell) = 8 And Not cell.HasFormula Then
 GetFormulaI = "'" & cell.Formula
Else
 GetFormulaI = cell.Formula
End If

Answer 2

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.

Edit: I've just realised that you could just 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.

Answer 3

Look at the PrefixCharacter property of the Range object which corresponds to that cell

From the help:

If the TransitionNavigKeys property is False, this prefix character will be ' for a text label, or blank. If the TransitionNavigKeys property is True, this character will be ' for a left-justified label, " for a right-justified label, ^ for a centered label, \ for a repeated label, or blank.

The TransitionNavigKeys part relates to Lotus 1-2-3 compatibility so it's more than likely going to be False

Answer based on article at:

http://excel.tips.net/Pages/T003332_Searching_for_Leading_Apostrophes.html

(warning: slightly annoying pop-up may appear)

---

edit: actually this probably isn't going to be any use because PrefixCharacter is read-only :(

edit2: I was right the first time. PrefixCharacter only gets populated if the value added to the cell started with ' so just read back PrefixCharacter plus Value and concatenate. As long as TransitionNavigKeys is False, that is

Answer 4

You might try pre-pending a single quote to your text fields ( '''' + dbField ) in your query so that for fields with embedded single quotes your query would return:

"''stuff in single quotes'"

which when placed in an Excel cell would convert to:

"'stuff in single quotes'"

for characters that weren't in quotes you would get:

"'stuff that wasn't in quotes"

which when placed in an Excel cell would convert to:

"stuff that wasn't in quotes"

Worth a shot. :-)

Ron