Leading Apostrophe

[AlexanderBB]

I'm comparing database against an excel worksheet range. The range value ignores the Leading Apostrophe which makes the compare fail as the db does include it.
Is there any method for Excel to detect it's presence ?

 

[Giordano Bruno]

Hi ABB. This probably won't help, but if the values are actual values, a formula like =IF(ISTEXT(A1),VALUE(A1),A1) would remove leading apostrophes.

 

[Alex Blakenburg]

What is that you want to know that is different from the last time you asked this question a few years ago ?
Identify cells with leading apostrophe via VBA
You seemed to indicate that you found this helpful.
Searching for Leading Apostrophes

Essentially the only way to identify it was in VBA to use (taking the Activecell as a sample)

Debug.Print ActiveCell.PrefixCharacter

Is it an option to go the other way and to remove the "'" from the db record (if you are putting it into a recordset or array first) before doing the comparison ?

 

[AlexanderBB]

Thanks, I'll chck out .PrefixCharacter. Could be handy - didn't know about that or had forgotten. YesI was detecting it from the db record but handy to have an alternative.

 

[Alex Blakenburg]

The prefix method is the only way I can find of detecting the leading Apostrophe, so for a match like you are after you would be looking at doing something like this:

Sub CompareText()

    Dim dbText As String
    Dim cellText As String
    Dim rCell As Range
    
    dbText = "'Text String"
    Set rCell = ActiveCell
        
    cellText = rCell.Value
    If rCell.PrefixCharacter = "'" Then
        cellText = "'" & cellText
    End If

    If cellText = dbText Then
        MsgBox "Exact Match"
    Else
        MsgBox "Difference"
    End If

End Sub

 

[AlexanderBB]

Thanks Alex all sorted. Looks like I did know this but had forgotten it.

 

[Alex Blakenburg]

All good. It was nearly 4 years ago.