Yongle
02-24-2015, 05:14 AM
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
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