Replace in string if almost a match

[wackywoo105]

I want to remove:

Dr P K Jain & Dr T Subramanian

from:

Dr P Jain & Dr T Subramanian, Little Lever Health Centre, Mytham Road, Little Lever, Bolton, Lancashire, BL3 1JF

leaving just the address.

string1 = "Dr P K Jain & Dr T Subramanian" & ", ")
string2 = "Dr P Jain & Dr T Subramanian, Little Lever Health Centre, Mytham Road, Little Lever, Bolton, Lancashire, BL3 1JF"
string2 = Replace(string2, string1, "")

The data is scraped from a website. The problem is the string at the top has and extra letter "K" in it so Replace() doesn't work. Is it possible to have a replace function for a percentage match?

 

[theDBguy]

Hi. We'll probably need more data, but from the one you posted, it looks like you should be able to use the Mid() function. For example:

Mid(string2, InStr(string2,",")+2)

Hope that helps...

 

[CJ_London]

I suspect this is one example of potentially 100's of variations so cannot see how you can make replace work.

Why replace the K - what makes it wrong?

If it is just the address that you want use the mid function to remove everything before the first ,

newstr=mid(oldstr,instr(oldstr,",")+2)

but even this is not guaranteed if there is no comma, or there is a comma in the name part

 

[wackywoo105]

I'm scraping data from here: https://www.nhs.uk/service-search/find-a-gp/results/BL3

It returns the practice name, address and phone all separately. The "Cleggs Lane Medical Practice/129" is easy to fix. The problem with the one above is the practice name is also in the address, but with a very slight difference so is duplicated. I've already fix the "and" and "&" but the "K" would only be specific to this entry.

 

[CJ_London]

think there are so few it will be quicker for you to just do a manual check and correction

You could perhaps limit the ones to check by focusing on those where the first 4 or 5 characters of the name match the first 4 or 5 characters of the address - but even this will not be foolproof.

 

[wackywoo105]

Thanks all. Having thought a bit more it only matters if the first few characters match so I have gone with this:

If Left(pracname, 4) Like Left(pracadd, 4) Then
    pracadd = Right(pracadd, Len(pracadd) - (InStr(pracadd, ",") + 1))
End If

 

[CJ_London]

suggest use = rather than like

If the fields are indexed you might find this faster

pracname Like Left(pracadd, 4) & "*"

but no point if the fields aren't indexed

Also, not sure why you are using vba - just use a query

SELECT *
FROM myTable
WHERE pracname Like Left(pracadd, 4) & "*"

 

[arnelgp]

is there always a comma (,) after the name, then thats the
clue:

string1 = "Dr P Jain & Dr T Subramanian, Little Lever Health Centre, Mytham Road, Little Lever, Bolton, Lancashire, BL3 1JF"
stringNew = TrimMid(string1, Instr(string1, ",") + 1))