Using the Like function to remove unwanted text from a range of cells

[SternHammer]

So I am very new to VBA (though I am modertaely famuiliar with Excel) but I cannot for the life of me figure out why this isn't working.

I have a range of cells with addresses and postcodes in them, though some have 2 lines in the street address and some 3:

12 Easy Street
Lazy Town
ZX3 5PD

Or

15 Hard Street
Lazy Town
Nothingshire
ZX7 4PD

What I want to do is have the macro search the cell looking for the pattern [Capital Letter][Capital Letter][Number] and delete only the matching text (anything after the matching text being deleted is also fine).

My plan was to use Like to match the pattern and then use range.replace (or similar, I haven't worked this part out yet) to replace the matched pattern with a space (to effectively delete it). What I have so far is:

Sub PatternMatch()

    Dim cell As Range
   
    For Each cell In Range("E7:E10")
       
        If cell Like "[A-Z][A-Z]#*" Then
            Debug.Print cell
        Else: Debug.Print "Check Fail"
        End If
    Next
End Sub

This is just returning a "check fail" for every cell in the range (so I haven't even looked at how to link the Like to the replace). It works fine when the postcode is the first text in the cell, but as soon as there is other text in front of it it fails. This feels like I am missing something very obvious, shouldn't Like check the entire string for the pattern? It's driving me nuts.

 

[par60056]

Could you be running into a problem because "cell" has meaning to VBA? I would not use that as a variable.

 

[SternHammer]

Unfortunately no, changing "cell" to "CustAddy" (Which I am pretty sure has no meaning to VBA ) yields the same results.

 

[par60056]

I entered your addresses and typed your code (no copying) and it worked perfectly.

Are you sure the "Z" in the LIKE are caps? Is it possible that there is a space or something else at the beginning of the field?

 

[Fluff]

How about

        If cell Like "*[A-Z][A-Z]#*" Then

 

[SternHammer]

How about

        If cell Like "*[A-Z][A-Z]#*" Then

Yup that was it, many thanks

 

[Rick Rothstein]

Could you be running into a problem because "cell" has meaning to VBA? I would not use that as a variable.

Cell has no meaning to VBA... you are thinking of Cells (with an "s" on the end) which does have meaning to VBA. I use Cell as a variable all the time and have never had a problem yet.

 

[SternHammer]

Still no luck with the use of Replace. It's not throwing any errors but it doesn't change anything in the cells. Can Replace handle patterns like "[A-Z][A-Z]" and I am just ignorant of the syntax or is there a better suited method for removing specific patterns of characters from cells?

 

[Rick Rothstein]

Just so I am clear on what you are trying to do, am I correct in assuming that if an address has a postal code you want it removed?

 

[SternHammer]

Thanks Rick. Yes, it's to remove a postal address (although the functionality itself is what I am really interested in).
Due to the tight grouping of address they all have the format "[A-Z][A-Z]# #[A-Z]{A-Z]"