CODEWITHSUNDEEP
excelvbavstoworksheet-function
Smack
Smack

Reputation: 946

formula for validating only string value in excel

I want user to enter only characters in excel cell.. i used ISTEXT(value) in custom formula of excel but can't get correct output for string like hgdashj2132154 please help... thank u..

Upvotes: 1

Views: 4136

Answers (3)

Marc Thibault
Marc Thibault

Reputation: 1728

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

Upvotes: 3

Nick Spreitzer
Nick Spreitzer

Reputation: 10598

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

Upvotes: 1

PowerUser
PowerUser

Reputation: 11791

Smack, I found this on my bookmarked sites. It's essentially the same concept as Marc Thibault's suggestion, but doesn't require any additional references:

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

Upvotes: 2

Related Questions