CODEWITHSUNDEEP
excel
AshBestos
AshBestos

Reputation: 302

How do I determine if a cell contains non-western text? (Excel)

I have a column containing titles that feature both English / basic Latin character-based titles e.g: John Smith and non-western / extended Unicode character-based titles e.g: 黄小琥 OR Björk

How can I check whether a cells in column B are the 'plain' English titles or the opposite?

Any pointers / suggestions would be gratefully received!

Thanks in advance.

Upvotes: 4

Views: 25456

Answers (2)

techjp
techjp

Reputation: 125

When I tried @lori_m's VBA code on strings that have mixed English and Japanese I got an overflow. Not sure exactly why. I modified it slightly and it works for me now:

Private Function is_latin(ByVal str As String) As Boolean
    Dim i As Integer

    is_latin = True

    For i = 1 To Len(str)
        If Abs(AscW(Mid(str, i, 1)) - 64) >= 64 Then
            is_latin = False
            Exit For
        End If
    Next i
End Function

It also exits with false the first time it finds a non-latin character. This isn't the best type of coding practice in a FOR loop but it will speed up processing for longer strings.

Upvotes: 1

lori_m
lori_m

Reputation: 5567

You could enter this UDF in a VBA code module. 

Function IsLatin(Str As String) As Boolean
IsLatin = True
For i = 1 To Len(Str)
    IsLatin = IsLatin And Abs(AscW(Mid(Str, i, 1)) - 64) < 64
Next i
End Function

Then if your text is in Column A, enter in cell B1 =IsLatin(A1) and fill down.

...alternatively in Excel 2013 you could use this array formula confirmed with CTRL+SHIFT+ENTER:

=AND(UNICODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<128)

also filled down from B1.

Note: CODE can't be used for this as it translates unicode to latin letters, for example =CODE("α") and =CODE("a") both return 63

Upvotes: 7

Related Questions