如何确定一个单元格是否包含非西方文本? (EXCEL) [英] How do I determine if a cell contains non-western text? (Excel)
问题描述
我有一列包含英文/基本拉丁字符的标题的标题,例如:约翰·史密斯和非西方/扩展的基于Unicode字符的标题,例如:黄小琥 OR Björk
如何查看B列中的单元格是否为简单英文标题或相反?
任何指针/建议将被感激地收到!
提前感谢
您可以输入此UDF一个VBA代码模块。
函数IsLatin(Str As String)As Boolean
IsLatin = True
For i = 1 To Len (Str)
IsLatin = IsLatin和Abs(AscW(Mid(Str,i,1)))64) 64
下一个i
结束函数
然后如果你的文本在列A ,输入单元格B1 = IsLatin(A1)
并填写。
...或者在Excel 2013中可以使用CTRL + SHIFT + ENTER确认的数组公式:
= AND(UNICODE(MID(A1,ROW(INDIRECT & LEN(A1))),1))< 128)
也从B1填满。 b
$ b
注意: CODE
不能用于此,因为它将Unicode转换为拉丁字母,例如 = CODE(α)
和 = CODE(a)
都返回63
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.
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
这篇关于如何确定一个单元格是否包含非西方文本? (EXCEL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!