asked on

Use VBA to check if a cell in Excel contains letters

What is the simplest way to tell if a cell contains any letters? I'm not looking for specific letters and it doesn't matter if the cell contains any numbers or special characters. If the cell contains one or more of the letters from A-Z, I would like the test to be positive regardless of whatever else is in the cell.

Thanks in advance.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Professor J

if you have different text and numbers in different cells, then select them and then run the macro, then it will tell which cell address whether it is text or whether it is numeric

zorvek (Kevin Jones)

This version handles empty cells:

=IFERROR(IF(SUMPRODUCT((CODE(LOWER(MID(A1,COLUMN(INDIRECT("A:"&CHAR(64+LEN(A1)))),1)))=ROW($96:$123))*1)>0,"Contains characters","Does not contain characters"),"Cell is empty")

In the formula, the reference A1 is the cell being evaluated. Change as required.

Kevin

zorvek (Kevin Jones)

I neglected to see the desire for a VBA solution.

Kevin

Professor J

yes, Zorvek.

i totally agree with you. when it can be done using built in functions, why to overkill it with VBA.

your solution is good

zorvek (Kevin Jones)

Here is a VBA function you can use to test specifically for upper and lower case letters:

Public Function HasCharacters(ByVal Text As String) As Boolean

Dim Position As Long

HasCharacters = True

For Position = 1 To Len(Text)
If UCase(Mid(Text, Position, 1)) >= "A" And UCase(Mid(Text, Position, 1)) <= "Z" Then Exit Function
Next Position

HasCharacters = False

End Function

Use it like so:

If HasCharacters(CellReference.Value) Then

Kevin

SOLUTION

ThomasMcA2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zorvek (Kevin Jones)

A couple of points. The "Like" operator is not Regular Expressions. It is powerful though but, to provide the desired result, we have to include the asterisk on both ends to account for other characters. And it does work in all versions of Excel.

Public Function HasCharacters(ByVal Text As String) As Boolean

If Text Like "*[a-zA-Z]*" Then
HasCharacters = True
End If

End Function

Kevin

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

sai k

"This version handles empty cells:

=IFERROR(IF(SUMPRODUCT((CODE(LOWER(MID(A1,COLUMN(INDIRECT("A:"&CHAR(64+LEN(A1)))),1)))=ROW($96:$123))*1)>0,"Contains characters","Does not contain characters"),"Cell is empty")

In the formula, the reference A1 is the cell being evaluated. Change as required."

zorvek, can you please elaborate this?