Remove exact match only

Hi all

I am sure this has probably been asked before, but I have done a search and can't see anything that matches.

I am currently working with a database which includes addresses. These addresses include the "area" in one of the cells, but I would like to remove all references to the area from the data. Unfortunately, the "area" could be in any of the address lines (so Address1, Address2, Address3, etc).

I am using this syntax -

Cells.Replace What:="Northway", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


(Northway is an example, not a real area - there are about 50-60 different areas that I need to remove).

For instance, an address could be 1 The Avenue, Northway, Mytown - where "Northway" is the area and is in Address2. Or it could be Flat 1, 1 The Avenue, Northtway, Mytown, where Northway is in Address3. However, that's straightforward enough. My problem comes when I have an address such as 1 Northway Avenue, Southway, Mytown. I have already established that Northway is an area and so should be removed, but as it is also part of the string in Address1, it's also getting removed from there as well; changing the data to read 1 Avenue, Southway, Mytown.

I hope this explains the issue - I'm not terribly proficient in VBA so would be grateful for any advice, thoughts or ideas, or to be pointed to any previous similar questions.

Many thanks

Ed

The trouble you're going to have is that without any proper data governance you're essentially going to have to write a rule for every possible scenario.

I think you're also going to have to scan through each field character by character so you can put in functions to test if, for example, the matching value has a number 2 characters before it in the field, ie it's a street address.

Question: In your fields, are all address components always separated by a comma? If so, you can look for commas.....

Have a read of: https://www.techonthenet.com/excel/formulas/mid.php

Thank you for the reply which sort of confirms my worst fears. Unfortunately, the data is exceptionally poor quality - it's been entered in numerous different ways by any number of different users and there is no way of really separating each aspect of the address - commas would have been nice, but they are a rare commodity and highly prized in this office...you'd almost think that the company had bought keyboards without them! In practice, this means that I will need to sift through the cells manually anyway, it would just have been nice to automate a little bit of the process.

I have not read the text description, but as per the header, if you want to an exact match.

Change this line

xlPart

INTO

xlWhole

I still think looking for a number 2 chars before the start of the matching section of the field would filter out street names that match area names.

Amazing - exactly what I wanted...so simple too!! Thank you so much!!