Missing apostrophe

[Yongle]

Hi guys

I am working on an excel worksheet where the data has been imported from another database over which I have zero control. There are 2 descriptive fields containing text which often contain apostrophes input into the original database by a multitude of users. The apostrophes are never at the beginning of the field. (usually denotes possession as in < the girl's hat > )
The problem is that some apostrophes appear in Excel and some do not. The same record field sometimes contains one apostrophe but another one is missing.
In Excel, you cannot see a space, but if you put the cursor where the apostrophe should be, there is a (very thin!) character there that Excel does not display. I used ctrl+c and pasted this into Find&Replace and I can replace this character by doing this, but I want to automate this process via a macro and have failed miserably!
I used the CODE function, and that suggests that the ascii character is 146. But if I put this into vba and ask for chr(146) to be replaced , it does nothing. The code I used (which works if I try to replace other characters) is

Worksheets("Sheet2").Columns("K:L").Replace What:=Chr(146), Replacement:="XXX", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

If pasted into Word, the apostrophe is also missing but there is a visible space. Word does not recognise it as a space (hops over it if I use search and replace with ^w). Again the ctrl+c trick (mentioned above) works.

As an example of my problem, in the attached 2 files there should be an apostrophe between "Britain" and "s".

Any ideas as to what else I can try?
Hopefully I have posted correctly - this is my first attempt.
Thanks
Yon

[NoSparks]

Running this bit of code on what you posted in the .xlsx, on my computer it is chr(63)

Sub CheckOfCharacters()
    Dim i As Integer
    Dim str As String

str = ActiveCell.Value

    For i = 1 To Len(str)
        Debug.Print Mid(str, i, 1) & "  =  " & Asc(Mid(str, i, 1))
    Next

End Sub

[Yongle]

Thanks NoSparks for your help but Excel does not believe that either. I ran =SUBSTITUTE(L8,CHAR(63),"xxx") against various cells containing the missing apostrophe and Excel still refuses to do replace anything! Macro also.
Any other ideas
thank you
Yon

[NoSparks]

There has been a discussion on one of the other Excel forums in the last few days of something somewhat similar dealing with chr(216) which is Ø. I haven't been able to relocate it yet to see if anything there would be of relevance.

[Yongle]

Thanks NoSparks.
This seems to be one of those frustrating problem which appear obvious but for some reason are not!
Yon

[Paul_Hossler]

Use

What:=ChrW(146)

and see

You're dealing with Unicode Wide characters

[Yongle]

Thank you Paul. Absolutely spot on!!
Yon