当前位置：文江博客话题详情

excel中仅验证字符串值的公式

发布于 2024-10-10 14:43:38 字数 140 浏览 5 评论 0原文

我希望用户只在 Excel 单元格中输入字符。我在Excel的自定义公式中使用了ISTEXT(value)，但无法获得像hgdashj2132154这样的字符串的正确输出，请帮忙... 感谢你..

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

难如初 2024-10-17 14:43:38

只需要一点正则表达式魔法即可。您需要一个用户定义的函数和正则表达式插件。在“开发人员”视图中，转到“工具”|“参考”并勾选“Microsoft VBScript 正则表达式 5.5”。

这是函数：

Public Function isOnlyAlpha(myText) As Boolean
   Dim regEx
   Set regEx = New RegExp   'Regular expression object
   regEx.Pattern = "^[a-zA-Z]*$"  ' Set pattern.
   isOnlyAlpha = regEx.Test(myText)   ' Test for match
End Function

结果：

=isOnlyAlpha("hgdashj2132154")
False

=isOnlyAlpha("hgdashj")
True

如果您不想使用正则表达式，那么总是有“Like Loop”：

Public Function IsOnlyAlpha(Value As String) As Boolean
    IsOnlyAlpha = True
    Dim i As Integer
    For i = 1 To Len(Value)
        IsOnlyAlpha = IsOnlyAlpha And (Mid(Value, i, 1) Like "[A-Za-z]")
    Next i
End Function

It just takes a little regular expression magic. You need a user-defined function and the regular expression add-in. In the Developer view go to Tools|References and tick Microsoft VBScript Regular Expressions 5.5.

Here's the function:

Public Function isOnlyAlpha(myText) As Boolean
   Dim regEx
   Set regEx = New RegExp   'Regular expression object
   regEx.Pattern = "^[a-zA-Z]*$"  ' Set pattern.
   isOnlyAlpha = regEx.Test(myText)   ' Test for match
End Function

The results:

=isOnlyAlpha("hgdashj2132154")
False

=isOnlyAlpha("hgdashj")
True

If you'd rather do without regular expressions, there's always the "Like Loop":

Public Function IsOnlyAlpha(Value As String) As Boolean
    IsOnlyAlpha = True
    Dim i As Integer
    For i = 1 To Len(Value)
        IsOnlyAlpha = IsOnlyAlpha And (Mid(Value, i, 1) Like "[A-Za-z]")
    Next i
End Function

回复收藏 0 原文

贵在坚持 2024-10-17 14:43:38

哎呀，我在我的书签网站上找到了这个。它本质上与 Marc Thibault 的建议相同，但不需要任何额外的参考：

http: //www.techonthenet.com/excel/formulas/alphanumeric.php

回复收藏 0 原文

星星的轨迹 2024-10-17 14:43:38

纯 VBA 方法：（

我编辑了答案以使用 Like 运算符而不是 IsNumeric 函数。现在只有字符 a 到 z 被视为有效，而不仅仅是非数字字符。）

Public Function IsOnlyAlpha(Value As String) As Boolean

If Len(Value) = 0 Then

    IsOnlyAlpha = False
    Exit Function

End If

Dim i As Integer
For i = 1 To Len(Value)

    If Not Mid(Value, i, 1) Like "[A-Za-z]" Then

        IsOnlyAlpha = False
        Exit Function

    End If

Next i

IsOnlyAlpha = True

End Function

A pure VBA approach:

(I edited my answer to use the Like operator instead of the IsNumeric function. Now ONLY characters a to z are considered valid, instead of just non-numeric characters.)

Public Function IsOnlyAlpha(Value As String) As Boolean

If Len(Value) = 0 Then

    IsOnlyAlpha = False
    Exit Function

End If

Dim i As Integer
For i = 1 To Len(Value)

    If Not Mid(Value, i, 1) Like "[A-Za-z]" Then

        IsOnlyAlpha = False
        Exit Function

    End If

Next i

IsOnlyAlpha = True

End Function

回复收藏 0 原文

~没有更多了~