I was asked today if Excel through the use of standard formulae could determine if the first character of another referenced cell was an alphabeta character.
The first thought I had was to see if Excel had any native formulae (RegEx) for doing this, but no.
My second thought was to test the cell using LEFT and CODE to check the first character was between 65 and 90 or 97 and 112. Here’s what I first came up with:
=IF(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),"TRUE","FALSE")
Obviously this first attempt only handles capital letters so I expanded it to:
=IF(OR(AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90),AND(CODE(LEFT(A1,1))>=97,CODE(LEFT(A1,1))<=112)),"TRUE","FALSE")
This works but very inflexible for future use, so I suggested a VBA formulae as follows:
=IF(MyLikeFunc(A1,"[A-Z]*"),"TRUE","FALSE")
In this function the first reference is the cell text to be checked and the second term is the pattern to match the information. Below is the definition of the MyLikeFunc() function in VBA:
public Function MyLikeFunc(rngRef As Range, strPattern As String) As Boolean MyLikeFunc = (rngRef.Value Like strPattern) End Function
This function basically exposes the VBA Like operator to the worksheet as a function, so for more information on the patterns it can match please refer to the VBA help for the “Like” keyword.
This function then becomes more flexible as you can define the pattern matching in the call to the function. There is one thing to note here, a VBA function is considerably slower than a native Excel function, so use of these types of functions should be balanced between flexibility and speed.