Is there an easy way to check if the string starts with letters (any 4 letters)?

Question

is there a way to check if the string begins with any 4 letters. I am looking for something like this:

If string like "####*" then
'DO STUFF
end if

"#" is for digits, I need the same thing but for letters only. Can this be done without regEx?

Answer 1

I don't know a way to do this without using regular expressions. We can try using regex Test along with the pattern ^[A-Z]{4}.*$:

Dim input As String
Dim regex As Object
Set regex = New RegExp

regex.Pattern = "^[A-Z]{4}.*$"
input = "ABCD blah"

If regex.Test(input) Then
    'DO STUFF
End If

Answer 2

You can do it with Like almost the same as with RegEx.

"{#}" - doesn't exist in Like operators, but "[A-Z]" absolutely valid

if string like "[A-Z][A-Z][A-Z][A-Z]*" then
   'DO STUFF
end if

Answer 3

Can this be done without regEx?

Yes, there is no specific need for Regular Expressions since the Like operator is quite capable as some sort of last resort to handle the situation, just like the writer of this article explains. Also, RegEx is sort of slow on a larger database. Nonetheless, RegEX is a great tool to use!

The solution provided by @AlexandruHapco would tell you if the string starts with 4 capital letters. But to account for lower OR upper, you can extend this logic:

If str Like "[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]*" Then

However, to shorten this a bit we can use [!charlist] to tell the operator we are looking for something that is NOT in the provided range. In other words, we could use:

If str Like "[!0-9][!0-9][!0-9][!0-9]*" Then

This last solution won't work when your string has any other characters than alphanumeric ones.

Answer 4

Approach using the FilterXML function

The WorksheetFunction FilterXML() has been added in ►Excel 2013 and allows to specify any XPath search string for a given XML document, which hasn't to be a locally saved file (needing WebService() function), but can be a string within well formed opening and closing nodes, i.e. our test string with some easy node additions (partly comparable to a html structure).

Example call

Sub TextXML()
Dim myString As String
myString = "ABCD blah"
If check(myString) Then
   'DO STUFF
   Debug.Print "okay"
Else
   Debug.Print "oh no"
End If
End Sub

Help function

Function check(ByVal teststring As String) As Boolean
    Const s As String = Chr(185)  ' unusual character, e.g. Chr(185): "¹"
    On Error GoTo oops
    If Len(WorksheetFunction.FilterXML("<all><i>" & teststring & "</i></all>", "//i[substring(translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','" & _
       String(26, s) & "'),1,4)='" & String(4, s) & "']")) > 0 Then check = True
    Exit Function
oops:
    Err.Clear
End Function

tl;tr - how to use VBA in Excel versions before 2013

For the sake of the art the classic way to use XPath via XMLDOM methods:

Example call

Sub TextXML2()
Dim myString As String
myString = "ABCD blah"

If check2(myString) Then
   'DO STUFF
   Debug.Print "okay"
Else
   Debug.Print "oh no"
End If
End Sub

Help functions

Function check2(ByVal teststring As String) As Boolean
' Purpose: check if first 4 characters of a test string are upper case letters A-Z
  ' [0] late bind XML document
    Dim xDoc As Object
    Set xDoc = CreateObject("MSXML2.DOMDocument.6.0")
  ' [1] form XML string by adding opening and closing node names ("tags")
    teststring = "<all><i>" & teststring & "</i></all>"
  ' [2] load XML
    If xDoc.LoadXML(teststring) Then
      ' [3a] list matching item(s) via XPath
        Dim myNodeList As Object
        Set myNodeList = xDoc.SelectNodes(XPath())
            'Debug.Print teststring, " found: " & myNodeList.Length
      ' [3b] return true if the item matches, i.e. the list length is greater than zero
        If myNodeList.Length > 0 Then check2 = True
    End If

End Function

Function XPath() As String
' Purpose: create XPath string to get nodes where the first 4 characters are upper case letters A-Z
' Result: //i[substring(translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹¹'),1,4)="¹¹¹¹"]
  ' get UPPER case alphabet
    Const ABC     As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  ' define replacement string consisting of an unusual character repeated 26 times
    Const UNUSUAL As String = "¹"       ' << replace by your preferenced character
    Dim replacement As String: replacement = String(Len(ABC), UNUSUAL)
    'return XPath string
    XPath = "//i[substring(translate(.,'" & ABC & "','" & replacement & "'),1,4)=""" & String(4, UNUSUAL) & """]"
End Function

Answer 5

To test a few characters -- the first 4 letters in this case -- you can always do the following:

If Not (Mid(string, 1, 1) Like "#" And Mid(string, 2, 1) Like "#" _ 
    And Mid(string, 3, 1) Like "#" And Mid(string, 4, 1) Like "#") Then  
    ' DO STUFF
End If

It's a bit more to type then when using the Like operator, but so what? Also, you can use Select Case in a loop... Another option is to use IsNumeric(Mid(string, i, 1)) instead of Mid(string, i, 1) Like "#", etc.

Granted, this approach is still quite practical with 4 characters, but is not as flexible and very much not scalable like RegEx is.