Check for same Names in cells

[DaveM]

Hi all

If John Smith is in A2 How can I check that John Smith is in a sentence in
K2.

I have other names in Columns so I'm probably looking for a formula to place
in L2 then copy down column.

Thanks in advance

Dave

 

[Rick Rothstein \(MVP - VB\)]

If John Smith is in A2 How can I check that John Smith is in a sentence in

K2.

I have other names in Columns so I'm probably looking for a formula to
place
in L2 then copy down column.

You might be able to use this...

=ISNUMBER(SEARCH(A2,K2))

However, be aware, it could produce false positives. For example, if the
text in K2 contained the name John Smithville, the formula would return TRUE
because the text "John Smith" is contained within the text "John
Smithville". If you knew that the **only** characters around the name you
wanted to find were blank spaces (no periods, commas, parentheses, etc.),
then you could find and exact match this way...

=ISNUMBER(SEARCH(" "&A18&" "," "&K18&" "))

If characters other than spaces are possible, then you would have to be able
to delineate all of them in order to modify the formula to work.

Rick

 

[Guest]

Dave,

try this formula in L2:

=IF(ISNUMBER(SEARCH(A2,K2,1)), TRUE, FALSE)


It will return TRUE if value in A2 is found in K2. Otherwise, it returns
FALSE. If you need it to be case sensitive, use FIND instead of SEARCH.

 

[Rick Rothstein \(MVP - VB\)]

try this formula in L2:

=IF(ISNUMBER(SEARCH(A2,K2,1)), TRUE, FALSE)


It will return TRUE if value in A2 is found in K2.

Since ISNUMBER returns TRUE or FALSE directly, you don't really need the IF
function call to repeat those results. This works exactly the same....

=ISNUMBER(SEARCH(A2,K2,1))

Rick

 

[Guest]

You're right, Rick. I think I first had it say Found/Not Found then changed
it to True/False but didn't realize I no longer needed the IF function. For
the OP, you'll only need the IF function call if you want it to say something
other than True/False in the cell.

=IF(ISNUMBER(SEARCH(A2,K2,1)), "Found", "Not Found")

 

[DaveM]

works fine

Thanks guys

You're right, Rick. I think I first had it say Found/Not Found then
changed
it to True/False but didn't realize I no longer needed the IF function.
For
the OP, you'll only need the IF function call if you want it to say
something
other than True/False in the cell.

=IF(ISNUMBER(SEARCH(A2,K2,1)), "Found", "Not Found")