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

> 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

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.


--
Hope that helps.

Vergel Adriano


"DaveM" wrote:

> 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
>
>
>
>
>

> 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

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")


--
Hope that helps.

Vergel Adriano


"Rick Rothstein (MVP - VB)" wrote:

> > 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
>
>

works fine

Thanks guys


"Vergel Adriano" <(E-Mail Removed)> wrote in message
news:87AA4A56-FBED-4D10-BA69-(E-Mail Removed)...
> 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")
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> > 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
>>
>>