Compare a list of strings against a list of keywords to return a partial match

**randomkiwi** · 10-09-2017, 10:56 PM

Hi all,

I'm attempting to clean up some data where I've been given a list of full street addresses (i.e. street number and name), but only want to extract (or confirm a match of) the street name. For example:

93 - 97 Harrison Street

Needs to just be: Harrison Street

105 Koromiko Road

Needs to just be: Koromiko Road

Cracroft Drive

Perfect as is

7 St Helens Place

Needs to just be: St Helens Place

I have a list of 700 or so streets, so could do a compare to this list, but I'm not sure exactly how to perform a partial match search that returns the above. Any help would be much appreciated!

**benishiryo** · 10-10-2017, 01:30 AM

hi there. you didnt input the desired results in the file though. do that so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand. based on your egs in the thread, it seems like the numbers are always in front and you want to remove them. so maybe:

Formula:

=TRIM(MID(A1,LOOKUP(2,1/ISNUMBER(--MID("0123456789"&A1,ROW(INDIRECT("1:"&LEN(A1)+10)),1)),ROW(INDIRECT("1:"&LEN(A1)+10)))-9,LEN(A1)))

**randomkiwi** · 10-10-2017, 03:50 PM

Hi benishiryo,

Sorry if I didn't make it clear what I was after. It is probably quite a difficult data set to work with (I have around 60000 addresses to convert), but now I'm wondering if it may be easiest if I were to
filter out streets that do contain a number and deal with them separately (i.e. with a separate formula) to streets that do not. I have added a few more examples in the attached file below. The example of
"State Highway 4" is unusual - there's only a couple of roads that have a number at the end, so I could manually adjust those if a formula can't be used to tackle this. I think a very challenging data set!

**benishiryo** · 10-11-2017, 05:06 AM

hmmm. it seems like you want to remove numbers WITH the text if it's at the beginning of the address. and ignore it if it's at the end. this formula works for all your eg, but you probably need VBA to proceed further if it gets more complicated. post it in another thread if you require so. i might not be able to help with VBA.

with formula, try:

Formula:

=IFERROR(TRIM(MID(A2,FIND(" "," "&A2,LOOKUP(2,1/ISNUMBER(--MID("0123456789 "&A2,ROW(INDIRECT("1:"&LEN(A2)+11)),1)),ROW(INDIRECT("1:"&LEN(A2)+11)))-9),LEN(A2))),A2)

**Glenn Kennedy** · 10-11-2017, 05:16 AM

Download and install Microsoft Fuzzy look up

https://www.microsoft.com/en-gb/down....aspx?id=15011

Resuls seen in column B.

**randomkiwi** · 10-11-2017, 11:51 PM

Hi benishiryo and Glenn Kennedy,

benishiryo - Many thanks for the new formula. I've put this in and aside from only a couple hundred or so streets (out of 60000), it has done an excellent job of cleaning up the data.

Glenn Kennedy - I've got a fair bit more data coming up to "scrub", so I will give this a go soon. It appears to be a very useful tool. Thank you!

Compare a list of strings against a list of keywords to return a partial match

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Compare a list of strings against a list of keywords to return a partial match

Re: Compare a list of strings against a list of keywords to return a partial match

Re: Compare a list of strings against a list of keywords to return a partial match

Re: Compare a list of strings against a list of keywords to return a partial match

Re: Compare a list of strings against a list of keywords to return a partial match

Re: Compare a list of strings against a list of keywords to return a partial match

Thread Information

Users Browsing this Thread

Similar Threads

Compare Two columns, find a partial match and return occurrences

[SOLVED] compare list B with list A, return cell data on row where B matched A

index first charakter left or right of partial string from list of strings

[SOLVED] Compare strings for match in separate worksheet and return nearest future date

Find word(s) within a text string that match a list of keywords. (VBA)

Skakes in the Summer

Compare List A to List B and Return Duplicates to C

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions