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

SternHammer

New Member
Joined
Apr 7, 2020
Messages
7
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:

VBA Code:
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Could you be running into a problem because "cell" has meaning to VBA? I would not use that as a variable.
 
Upvote 0
Unfortunately no, changing "cell" to "CustAddy" (Which I am pretty sure has no meaning to VBA :p) yields the same results.
 
Upvote 0
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?
 
Upvote 0
How about
Rich (BB code):
        If cell Like "*[A-Z][A-Z]#*" Then
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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]"
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top