Block Finder - Like Function

Question

I have a large string (more than 255 char) called strBlockText. This string includes random text and block numbers. The block numbers should to be in the format ###Block####-## (IE: 245Block6533-56) but sometimes someone enters the wrong block number format in the text - for example ##Block####-## or ###Block###-## or ##Block###-##...etc.

**Note, this is for plain text only.

I want to write a function that will be able to state, "Wrong block number format identified." when the block number is fat fingered.

This is the text I'm using as a sample:

This is a Test that we need to figure out why this isn’t working. 24Block1234-23 This is a Test that we need to figure out why this isn’t working. 245Block4234-14 This is a Test that we need to figure out why this isn’t working. This is a Test that 245Block6533-56 we need to figure out why this isn’t working.

This is the code...that I feel should work but isn't:

Dim strBlockText As String
Dim strBlockCheck As String

If (((strBlockText Like "*##Block####-##*") or _
     (strBlockText Like "*###Block###-##*") or _
     (strBlockText Like "*##Block###-##*")) And _
 (Not strBlockText Like "*###Block####-##*")) Then

    strBlockCheck = "Wrong block number format identified."

Else

    strBlockCheck = "Block number format acceptable."

End If

Would it be better to use a regex for this instead of like?...is there a reason like isn't working?

Your test string contains `This is a Test that 245Block6533-56` so your string *is* like `*###Block####-##*` -- so your code accepts it. Using a regex and iterating over the matches is the way to go — John Coleman, Jul 21 '20 at 22:40
Why is data entered into a complicated string and not just discrete values - a record for each value input? Could use an InputMask. — June7, Jul 22 '20 at 00:02
I havent played with input masks before. This text is entered like an essay though and sometimes my users will enter things wrong. — reickmey, Jul 22 '20 at 00:24

score 2 · Accepted Answer · answered Jul 21 '20 at 23:54

Consider this Sub using RegExp object with late binding:

Sub testRegExp2(strS)
Dim regexOne As Object, Matches As Object, Match As Object
'Set regexOne = New RegExp
Set regexOne = CreateObject("VBScript.RegExp")
regexOne.Pattern = "[0-9]+Block[0-9]+-[0-9]+"
regexOne.Global = True
Set Matches = regexOne.Execute(strS)
For Each Match In Matches
    If Not Match Like "###Block####-##" Then
        Debug.Print "Wrong block number format identified: " & Match
    Else
        Debug.Print "Block number format acceptable: " & Match
    End If
Next
End Sub

June7, as always you are the best. Thank you very very much! — reickmey, Jul 22 '20 at 00:23

Block Finder - Like Function

1 Answers1