CODEWITHSUNDEEP
vbaexcel-formulaunicodecharacter-encodingchinese-locale
SoftTimur
SoftTimur

Reputation: 5510

How to check if a cell value is Chinese text in Excel

I have a list of cells which contain the first name of users: Amy, Jim, 梅, 明, ธนกาญจน์, Андрей, etc. From the name, I would like to determine if a user is Chinese.

Does anyone know if there is any formula or VBA method to determine this?

Upvotes: 0

Views: 2391

Answers (1)

Dhay
Dhay

Reputation: 621

Since the question is to check based on Chinese text I would prefer using AscW function like below

Function DetectChineseText(cel As Range) As Boolean
DetectChineseText = True
For i = 1 To Len(cel.Value)
  MidChar = Mid(cel.Value, i, 1)
  If Not (AscW(MidChar) >= 19968 And AscW(MidChar) <= 25343) Then
    DetectChineseText = False
    Exit For
  End If
Next i
End Function

But as per this link there are 89,602 codepoints assigned in Unicode character set for chinese characters we need to mention every range we needed to detect in the data. For now I just used only one range (19968 to 25343) as per a wikipedia article.

You could add the range as per your requirement.

Upvotes: 1

Related Questions