如何用公式判断Excel单元格中的字符串是英文字母?

发布网友发布时间：2022-05-05 17:57

共2个回答

热心网友时间：2022-06-27 21:26

你这个难度确实太高了，不自定义函数真没办法，下面出两个馊主意：

一、替换所有字母以后，看是否为空，比如判断A1的公式为：
=""=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
UPPER(A1),"A",""),"B",""),"C",""),"D",""),"E","")
......

二、替换所有特殊字符以后，看是否和原来相同，比如判断A1的公式为：
=A1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
UPPER(A1),"0",""),"1",""),"2",""),"3",""),"4","")
......

热心网友时间：2022-06-27 21:26

难度确实太高了。楼上的办法管用，只是性能上可能有问题。呵呵

还是用自定义函数吧。正好研究正则表达式。
可以参考如下：

Function judgeStr(strString As String) As Boolean
Dim objRegExp As RegExp
Set objRegExp = New RegExp
With objRegExp
.IgnoreCase = True
.Global = True
.Pattern = "^[A-Za-z]+$" '"[a-zA-Z]"
End With

judgeStr = objRegExp.Test(strString)

End Function

需要导入组件，工具-引用-Microsoft VBScript Regular Expressions 5.5

其实两层循环也行。