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

Question

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.

Answer 1

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