Turkish Excel version: transforming dotted upper i, "İ" to dotless upper i "I" with VBA

I identified a problem of special characters with “I” on a turkish version of Windows 10 with a Turkish Excel version. “i” gives another letter in Turkish when it is translated to uppercase: “İ” and not “I”, as for instance when "i" is converted to upper case in English. The problem is that when I use non case sensitive Excel search formulas (for instance “match” or “countif” formulas), the Turkish Excel will look for “i” or “İ” (with a dot), which it doesn’t find as the letters are uppercase in the lookup range (and there's no "İ"), while the English version look for “i” or “I”, which they’ll find.

To summarize, I search for "i" in a non-case sensitive way, and my Turkish colleague doesn't find any result, because his computer looks for "i" and "İ", and all the expected results are with "I".

I cannot ask the users having this problem to change the language in Excel or in Windows, nor change the lookup source or target ranges. But I can change the formulas (match and countifs are used).

I'm not sure where this lower to uppercase conversion is coming from, if it's from Excel or Windows. But after installing a Turkish version of Excel on a German version of Windows, I didn't have the problem. So I presume the problem is coming from Windows (some language settings, in the end, knowing that is interesting but won't help much)...

I was thinking to writ a VBA formula to change texts to uppercase AND change dotted capital “İ” to dotless capital “I” with replace, then use this string in my search functions. But I cannot find the dotted capital “İ” in the Chr() formula... I think the Chr() function doesn't use the extended ASCII characters, just the standard ones. See the kind of function I intended to use below.

Function upper_i(myStr As String) As String

upper_i = UCase(myStr)
upper_i = Replace(upper_i, Chr(???), "I")

End Function

How can I tell Excel I want a dotted capital "İ" here? Thanks for your help!

Solution

Assuming I'm reading correctly, maybe try:

Function upper_i(myStr As String) As String
    upper_i = UCase$(myStr)
    upper_i = Replace(upper_i, ChrW(304), "I")
End Function

Seems to pass the test below at least:

Private Sub TestFunction()
    Dim someText As String
    someText = "ok, ok, " & ChrW(304)

    Debug.Assert someText <> "OK, OK, I"
    someText = upper_i(someText)
    Debug.Assert someText = "OK, OK, I"
End Sub

I didn't really understand why you're making the string uppercase, but maybe I need to read your question a few more times.