FUzzy matching - names (newbie help please)

[kongie]

Hi, I'm new to this type of query in Excel:


I have some data where I want to find and extract a list of names from cells in rows where any of the names match. For example the data is like:
Row 1:'Jon Smith 46 years old is but a new brown car'
Row 2:'Jonny smith 46 yrs likes brown cars'
Row 3:'Jon works at a garage repairing cars'
Row 4:'My mate Smithy loves those brown BMWS'


What expression would I need to search each row and then extract any of the following fuzzy matches (contained in a long list) :
Jon smith
Jonny smith
Jon
Smithy
And then return a new column with just those name, i.e. Actually display the names in a column next to column where name trxt was taken from?


The idea is that I extract data that might well be the same person in different phrases.


Thank you in anticipation, I'm new to this but very interested.

 

[snjpverma]

Try this
=LOOKUP(2^15,SEARCH({"Jon";"Jon smith";"Jonny smith";"Smithy"},A1),{"Jon";"Jon smith";"Jonny smith";"Smithy"})

 

[kongie]

Try this
=LOOKUP(2^15,SEARCH({"Jon";"Jon smith";"Jonny smith";"Smithy"},A1),{"Jon";"Jon smith";"Jonny smith";"Smithy"})

Thank you for this, really appreciated.

If you're able to help, is there anyway that it could include every variant so that if the row was:
row: Jon smith loves brown cars but liked to be called smithy. Some friends call him Johnny. He records his name surname first Smith, Jon.

so it returns in the next column: Jon smith, smithy, Johnny, Smith Jon.

if not thank you for your original reply to a newbie.

 

[snjpverma]

you are welcome Kongie.

Unfortunately, I have no idea how to get all the text in the same column for multiple match