Replace a character in VBA - HELP

[alrodopial]

Hi,
In cell A1 i have the name of a horse downloaded from RPS.
A1 = Notable D´Estruval
This horse name contains an apostroph, the "´" which is not the usual " ' ".
Whith the code formula it apears to be char(63).
I want to replace it with usual apostroph within VBA.
I get the error "sub or fuction not definied" and the "CHAR" is highligted.

Code: Select all

If InStr(Sheets("Temp").Range("A1").Value, CHAR(63)) = 0 Then .....


and the code stops without reaching the below "replace" code:

Code: Select all

Sheets("Temp").Range("A1").Value = Replace(Sheets("Temp").Range("A1").Value, CHAR(63), CHAR(39))

It apears that the "CHAR" is not supported from VBA (at this form).
Any ideas? Other way for the "CHAR"?
Thanks

[NorthView]

You should be able to use Replace on the cell in question.

[NorthView]

Sorry, I meant to say when you use Replace you should use the literal representation of the character instead of attempting to get its ASCII value ie "'" instead of CHAR(63).

[alrodopial]

you should use the literal representation of the character instead of attempting to get its ASCII value

Sorry forgot to mention that the question started because of the fact that:
You can not type the apostrophe in the VBA editor.
Even if you copy the apostrophe in excel and then paste it in VBA it is transformed into "?".
What i found so far:
There is "CHAR" formula in VBA and is the "Chr".
But if you use the below code :

Code: Select all

Sub testing1211()
   
    Sheets("Temp2").Select
   
    Current_horse = Sheets("temp2").Range("F2").Value
    apostrophe = Mid(Current_horse, 10, 1)
    Sheets("temp2").Range("F3").Value = Asc(apostrophe)
    If InStr(Current_horse, Chr(47)) = 0 Then
        Sheets("temp2").Range("F4").Value = "NOT FOUND"
    Else
        Sheets("temp2").Range("F3").Value = "FOUND"
    End If
   
    End Sub


It places in F3 the number "47" which supposed to be the ASCII for the apostrophe.
But in the next line the instr doesnt find the Chr(47).
?????

The way i managed it is the below, but if there is a simpler way please let me know:

Code: Select all

Sub REPLACE_APOSTROPHE()
   
    Sheets("Temp2").Select
   
    Current_horse = Sheets("temp2").Range("F2").Value
    ' replace all characters into capitals (the apostrophe turns into "/"
    Current_horse = StrConv(Current_horse, 1)
    ' replace the "/" with normal apostrophe and a blank
    Current_horse = Replace(Current_horse, "/", "' ")
    ' turn the first letter of every word into capital
    Current_horse = StrConv(Current_horse, 3)
    ' delete the extra blank that was added previus
    Current_horse = Replace(Current_horse, "' ", "'")
    Sheets("temp2").Range("F3").Value = Current_horse
   
    End Sub


In F2 the name of the horse
Thanks

[alrodopial]

I'm getting errors some times with the above code.
The name "Tom's Toybox" is wrongly transformed into "Tom'S Toybox"

It has to do with the blank i add and the turning all first letters into uppercase.
So any help will be wellcome.

[PeteB]

There was a post on here a while ago related to this - rather than trying to replace one apostrophe with another, it may be easier to convert all to upper case, and then strip out all characters other than AtoZ (i.e. even strip out spaces). That should give you the best possible chance of matching names that come from different places, with very little chance of a false match. Otherwise you will fix the apostrophe now, and then some other difference between the two systems will come along and catch you out in a few weeks...

[alrodopial]

it may be easier to convert all to upper case, and then strip out all characters other than AtoZ (i.e. even strip out spaces).

English isn't my native language, so
i assume "strip out" means "change".
How can i choose these characters in a string that are not letters?
And later transform them into something else?

[Ian]

Code: Select all

Dim MyChr1 As String
Dim MyChr2 As String

Sub SetChr()

    MyChr1 = Chr(63)
   
    MyChr2 = Chr(39)
 
    Range("A1") = WorksheetFunction.Substitute(Range("A1"), MyChr1, MyChr2)

End Sub

Does this work ?

[PeteB]

Sorry about that - I wouldn't have realised if you hadn't said!

By 'strip out' I mean remove. If a human is ever going to read these names, then yes you might want the apostrophes and spaces etc still in there, but if you are using a computer to compare strings from two different sources, then removing anything that might have been set up differently in the two sources will work better.

So you would be aiming to convert "Notable D´Estruval" to "NOTABLEDESTRUVAL", similarly you would convert "Notable D'Estruval" to "NOTABLEDESTRUVAL", and then you get a match.
(You could keep the spaces in if it made you happier, but you run the risk of one source using spaces where the other doesn't)

Ian's code should work - I will have a quick play and see if I can come up with something more generic.

The other thread I was thinking of is here:
http://www.gruss-software.co.uk/forum/v ... .php?t=439
It has some more examples of things you will need to strip out - like "(IRE)", so even if you have a generic solution, you will still need to handle these.

[Ian]

I've just realised Chr(63) is ? - are you sure you have the right value ?

[dgs2001]

Alrodopial

The code for the apostrophe is 96

if you want to find a code type the following in a cell

Code: Select all

=code("`")


and if you want to know what a code is use this

Code: Select all

=char(96)


Duncan

[alrodopial]

Hi, been away from yesterday evening so the lack of response.
To figure out what is going wrong i tried this code:

Code: Select all

Sub testingvvv()
   
    Sheets("Temp2").Select
    j = 1
    For j = 1 To 255
    Cells(j, 13).Formula = "=CHAR(" & j & ")"
    Cells(j, 14) = Chr(j)
    Next j
   
    End Sub


The same ascii numbers give in a couple of timaes different characters when used in a formula and in VBA.
Anyway i gave up because of this:
In F2 the name of the horse with the apostrophe.
If you copy-paste in F1 only the apostrophe taken from the F2 and then in another cell place the formula

Code: Select all

=SUBSTITUTE(F2;F1;"'")

the apostrophe is replaced correctly with the normal apostrophe.
But if instead of the first copy-paste you insert in F1 the apostrophe with VBA

Code: Select all

Range("F1").Value = Chr(180)

you see that the character in the F1 is the apostrophe you want to repalce BUT if you then place in another cell the same formula as above:

Code: Select all

=SUBSTITUTE(F2;F1;"'")

NO replacement is made.
So i give up. Any of you?

PeteB your idea about using only the letters and only Uppercase is excellent.
I'll try to see what i can do