CODEWITHSUNDEEP
vbaexcel
Eswemenasja
Eswemenasja

Reputation: 133

VBA check if a string follows pattern, space, two letters, six digits

I need to check if last 9 chars from cell follow a pattern. The searched pattern is space two letters and six digits. Cells contain some text then should have this pattern. Usually searched cell content looks something like this: "Tractor mowers PT009988" Regards Michał

Upvotes: 2

Views: 2388

Answers (2)

Martin Dreher
Martin Dreher

Reputation: 1564

Try this (if you want to include upper- and lower-case characters)

Dim c
For Each c In Selection
    If c.Value Like "* [A-Z,a-z][A-Z,a-z]######" Then _
        Debug.Print c.Value
Next c

https://msdn.microsoft.com/en-us/library/swf8kaxw.aspx

Upvotes: 3

Spurious
Spurious

Reputation: 2005

This will test for this.

Public Function RegExTest(sCellContent As String) As String
Dim sContent As String, sMatch As Variant, i As Long

sContent = Right(sCellContent, 9)

With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .Pattern = " [A-Za-z]{2}[0-9]{6}"
    If .test(sContent) Then
        Set sMatch = .Execute(sContent)
        RegExTest = sMatch(i)
        Exit Function
    End If
End With
End Function

This is the pattern that needs to be matched:
" [A-Za-z]{2}[0-9]{6}"

1 Space, 2 letters (both upper case and lower case) and six digits.

If in range A1 is the value Tractor mowers PT009988 and you put this formula in B1 =RegExTest(A1) then the output in B1 will be PT009988.

If you don't care whether or not this is in the last 9 characters then change sContent = Right(sCellContent, 9) to sContent = sCellContent

Upvotes: 3

Related Questions