Excel Encoding Comparrisons

[Helpme21]

Hello -

I have been having an issue comparing strings in excel because they are encoded different. I am pulling data from a database with the collation type utf8_general_ci and trying to compare it to another string pulled from the data in the excel sheet. The comparison statement always returns False, even if the strings have the same content because the two strings are encoded differently. Is there any way to convert the data pulled from the database into the same encoding method Excel uses as a standard or convert excels standard to utf8_general_ci?

Any other ideas on how to compare these strings would be helpful too.

Thanks in advance1
<dfn title="Unicode (multilingual), case-insensitive"></dfn>

 

[BiocideJ]

Not 100% sure of this, but how about enclosing both in CStr() functions..

i.e. IF CStr(UDFstring) = CStr(XLstring) Then ...

 

[Helpme21]

Not 100% sure of this, but how about enclosing both in CStr() functions..

i.e. IF CStr(UDFstring) = CStr(XLstring) Then ...

I just tried this and it did not work. This is the simple code I have for testing this. One of the strings is from the database and the other is inputted by me on the sheet.

Sub Check()

Dim d, d2 As String

d = UCase(Range("A1").Value)
d2 = UCase(Range("B1").Value)

If CStr(d) <> CStr(d2) Then
Range("C4").Value = "Fail"
End If

End Sub

 

[BiocideJ]

I wonder if there are other characters in there such as spaces that are throwing off the comparison...
Try this.

Sub Check()
 
Dim d, d2 As String
 
d = Trim(UCase(CStr((Range("A1").Value)))
d2 = Trim(UCase(CStr(Range("B1").Value)))
 
If d <> d2 Then
     Range("C4").Value = "Fail"
End If
 
End Sub

I am not really sure I understand what the difference is with the UDF-8 format that would prevent it from doing a straight comparison if this doesn't do it.

 

[Helpme21]

I wonder if there are other characters in there such as spaces that are throwing off the comparison...
Try this.

Sub Check()
 
Dim d, d2 As String
 
d = Trim(UCase(CStr((Range("A1").Value)))
d2 = Trim(UCase(CStr(Range("B1").Value)))
 
If d <> d2 Then
     Range("C4").Value = "Fail"
End If
 
End Sub

I am not really sure I understand what the difference is with the UDF-8 format that would prevent it from doing a straight comparison if this doesn't do it.

I just tried to trim the strings and it still fails. The reason I think it is failing is because the two strings are encoded differently, so the method excel uses to compare them results in it to think it's different, even if the content is both strings are the same.

 

[BiocideJ]

I just tried to trim the strings and it still fails. The reason I think it is failing is because the two strings are encoded differently, so the method excel uses to compare them results in it to think it's different, even if the content is both strings are the same.

Did some research online and found this on the web:
http://p2p.wrox.com/vbscript/29099-unicode-utf-8-system-text-utf8encoding-vba.html#post272370

It seems like this function may solve your problem.

e.g. UTF8_Decode(d) = d2