Formula that can compare cells and return results for partial matches

[Monty85]

Can anyone point in the right direction for a formula that can compare two cells and return a result for a partial match?

For example, I need to compare a list of names where one column may or may not contain a middle name. I.e;

A1 = John Smith
B1 = John F. Smith

I need a way to reference A1 and return a "True" result if the content of A1 is found somewhere in B1.

Appreciate any and all ideas.

 

[Monty85]

Book1
	A	B	C
1	John Smith	John F. Smith	TRUE
2	John Smith	John Smith	TRUE
3	John Smith	John Smiths	FALSE
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=OR(A1=B1,COUNTIF(B1,SUBSTITUTE(A1," "," * ")))

Had a quick test and this seems to do the job as well.

Thanks so much people - life savers.

 

[Georgiboy]

Yes mine is 365 only

 

[Monty85]

Book1
	A	B	C
1	John Smith	John F. Smith	TRUE
2	John Smith	John Smith	TRUE
3	John Smith	John Smiths	FALSE
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=OR(A1=B1,COUNTIF(B1,SUBSTITUTE(A1," "," * ")))

Just on this again - how would I make the formula work the other way as well.

So both the below examples should return TRUE

MERCER_BT_AUS_PEP_03042023 - Validations (less formulas).xlsx
	A	B	C
1	John Fred Smith	John Smith	FALSE
2	John Smith	John Fred Smith	TRUE
Sheet2

Cell Formulas
Range	Formula
C1:C2	C1	=OR(A1=B1,COUNTIF(B1,SUBSTITUTE(A1," "," * ")))

 

[JvdV]

Simply include the option inside OR():

Book1
	A	B	C
1	John Fred Smith	John Smith	TRUE
2	John Smith	John Fred Smith	TRUE
Sheet2

Cell Formulas
Range	Formula
C1:C2	C1	=OR(A1=B1,COUNTIF(B1,SUBSTITUTE(A1," "," * ")),COUNTIF(A1,SUBSTITUTE(B1," "," * ")))