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.

 

[Rick Rothstein]

I was not clear in my question... are you trying to remove just the postal code but leave the rest of the address or, if it has a postal code, do you want to remove the entire address (that is, clear the cell)?

 

[SternHammer]

Ah I see. Apologies for the misunderstanding. I am looking to remove the postcode (in this case by replacing it with a space) but leave the rest of the address.

 

[Rick Rothstein]

I am not sure why you want to replace it with a space rather than simply to delete the line, but as that is what you asked for, give this macro a try...

Sub PatternMatch()
  Dim Cell As Range, Arr As Variant
  For Each Cell In Range("E7:E10")
    If Cell Like "*[A-Z][A-Z]#*" Then
      Arr = Split(Cell.Value, vbLf)
      Arr(UBound(Arr)) = " "
      Cell.Value = Join(Arr, vbLf)
    End If
  Next
End Sub

 

[Peter_SSs]

Like Rick, it seems to me a bit odd replacing the postcode with a space as that will leave those rows with a Linefeed character and a space character after the town name.
Never-the-less if you are interested, here is another way to do what you have asked. If your range is large then the code can be re-written to process faster. Post back if that is required.

Sub RemovePostcode()
  Dim RX As Object
  Dim Cell As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "[A-Z]{2}\d.*"
  For Each Cell In Range("E7:E10")
    Cell.Value = RX.Replace(Cell.Value, " ")
  Next Cell
End Sub

If you did want to actually remove the postcode and leave those example cells with "Town" and "Nothingshire" as the very last characters in the cells then change the Pattern to

RX.Pattern = vbLf & "[A-Z]{2}\d.*"

and the Replace line to

Cell.Value = RX.Replace(Cell.Value, "")

 

[alansidman]

Your code is not asking it to remove anything. If you open the immediate window, you will see that when it achieves the required match it is printing the postal code in the Immediate window. This is exactly what you are asking the code to do. To delete the Postal Code then

Sub PatternMatch()

    Dim cell As Range
   
    For Each cell In Range("E7:E10")
       
        If cell Like "[A-Z][A-Z]#*" Then
            cell =""
        
        End If
    Next
End Sub

 

[Peter_SSs]

If you open the immediate window,

I think the OP has the immediate window open or else they would not be able to report that their code was

returning a "check fail" for every cell in the range

Given that, obviously the code was never reaching
Debug.Print cell
and therefore would never reach your cell = "" line either.
(I think the clues are in posts 5, 6 and beyond)

 

[SternHammer]

I am not sure why you want to replace it with a space rather than simply to delete the line, but as that is what you asked for, give this macro a try...

Sub PatternMatch()
  Dim Cell As Range, Arr As Variant
  For Each Cell In Range("E7:E10")
    If Cell Like "*[A-Z][A-Z]#*" Then
      Arr = Split(Cell.Value, vbLf)
      Arr(UBound(Arr)) = " "
      Cell.Value = Join(Arr, vbLf)
    End If
  Next
End Sub

The replacing with a space was to give me some flexibility. The formatting is (to put it mildly) a bit inconsistent sometimes there is only a space seperating them, sometimes a line break and sometimes a comma (these are sadly quite rare though).
Thank you for the help in any case. I wasn't able to get that macro to work unfortunately, running the macro cleared the entire cell and running it line by line did nothing (I have no idea how that makes any sense but it happens without fail).

Like Rick, it seems to me a bit odd replacing the postcode with a space as that will leave those rows with a Linefeed character and a space character after the town name.
Never-the-less if you are interested, here is another way to do what you have asked. If your range is large then the code can be re-written to process faster. Post back if that is required.

Sub RemovePostcode()
  Dim RX As Object
  Dim Cell As Range
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "[A-Z]{2}\d.*"
  For Each Cell In Range("E7:E10")
    Cell.Value = RX.Replace(Cell.Value, " ")
  Next Cell
End Sub

If you did want to actually remove the postcode and leave those example cells with "Town" and "Nothingshire" as the very last characters in the cells then change the Pattern to

RX.Pattern = vbLf & "[A-Z]{2}\d.*"

and the Replace line to

Cell.Value = RX.Replace(Cell.Value, "")

Thank you! This works just as I had hoped! I am not familiar with regular expressions so could I just check I understand the syntax being used? the [A-Z] searches for matches with capital letters; {2} is the number of characters to match to the previous parameter, \d is for numeric matching and . is for any character?
I presume the period is to allow the the whole postcode to be found rather than just the first section that exactly matches the pattern?
It's interesting to see that the pattern for replacing the whole last line searches for the postcode and then the next line (via vbLf) and then replacing it with nothing, thank you for that.


Final question: can you link multiple patterns like this within one macro (i.e. to search and replace a phone number and a postcode) or would you need to call multiple macros, which each do a single instance of match and replace?

 

[Peter_SSs]

Thank you! This works just as I had hoped!

You're welcome.

could I just check I understand the syntax being used? the [A-Z] searches for matches with capital letters; {2} is the number of characters to match to the previous parameter, \d is for numeric matching and . is for any character?
I presume the period is to allow the the whole postcode to be found rather than just the first section that exactly matches the pattern?

You have all that exactly right.
.. with the added bit that the * at the end of the pattern matches any number of the previous character (.)
That is to ensure we match from the beginning of the postcode to the end of the entire string (assuming nothing comes after the postcode).

It's interesting to see that the pattern for replacing the whole last line searches for the postcode and then the next line (via vbLf) and then replacing it with nothing,

You have that part the wrong way around though. The alternative pattern that I gave searches for a vbLf then the postcode & replaces it all with nothing.

can you link multiple patterns like this within one macro (i.e. to search and replace a phone number and a postcode) or would you need to call multiple macros, which each do a single instance of match and replace?

Hard to answer for sure without seeing say 5 or 6 lots of sample data, the expected results and an explanation in relation to that sample data.

If possible post your sample data and expected results with XL2BB so that we can easily see exactly what is in what cells, how the sheet is laid out and we can more easily copy the data for testing.

BTW, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.