i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

=IF(ISNUMBER(SEARCH("now",A1)),"Exists","Do not exist")

=IF(COUNTIF(A1:A1,"now")0,"Exists",Do not exist")

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"vito" wrote in message
...
i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

"Peo Sjoblom" wrote in message
...
=IF(ISNUMBER(SEARCH("now",A1)),"Exists","Do not exist")

=IF(COUNTIF(A1:A1,"now")0,"Exists",Do not exist")

--

Regards,

Peo Sjoblom


both returned "do not exist". i change a1 to a1:bm60000 in order to ensure
they search the whole sheet. just to remind that "now" is embedded in a text
and not a whole-cell content. thanks again

Hi!

Try this:

=IF(ISNUMBER(SEARCH(" now"," "&A1)),"Yes","No")

Biff

"vito" wrote in message
...
i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

Try this:

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"vito" wrote:

i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

I missed on: a nowhere man

We both miss on:

now, is the time
do it now!
"now" or never
etc

Punctuation marks always ruin the party!

Biff

"Ron Coderre" wrote in message
...
Try this:

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"vito" wrote:

i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be
returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron


In that way, i can't obtain the row number which matches. and how to make
the match case-insensitive in a handy way, ie. except input all the
combinations in the field?

True, Biff

If the desire is to properly catch the word in all possible scenarios...it's
time to graduate to regular expressions. Perhaps one of the R.E.
afficionados will post a solution..

***********
Regards,
Ron

XL2002, WinXP


"Biff" wrote:

I missed on: a nowhere man

We both miss on:

now, is the time
do it now!
"now" or never
etc

Punctuation marks always ruin the party!

Biff

"Ron Coderre" wrote in message
...
Try this:

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"vito" wrote:

i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be
returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

The afficionado will strike again!

Try this UDF with a reference to Microsoft VBScript Regular Expressions 1.0

'----------------------------------------------------------------------------------------------
Function FindWord(sWord As String, sText As String) As Boolean
Dim re As RegExp
Set re = New RegExp
re.IgnoreCase = True
re.Pattern = "\b" & sWord & "\b"
FindWord = re.Test(sText)
End Function
'-----------------------------------------------------------------------------------------------

HTH
--
AP

"Ron Coderre" a écrit dans le message de
news: ...
True, Biff

If the desire is to properly catch the word in all possible
scenarios...it's
time to graduate to regular expressions. Perhaps one of the R.E.
afficionados will post a solution..

***********
Regards,
Ron

XL2002, WinXP


"Biff" wrote:

I missed on: a nowhere man

We both miss on:

now, is the time
do it now!
"now" or never
etc

Punctuation marks always ruin the party!

Biff

"Ron Coderre" wrote in message
...
Try this:

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"vito" wrote:

i want to know whether a string is in a cell, built-in search
function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in
search
function, using formula of vlookup and find do not help." will be
returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.

Thanks. This was what I needed too.
WAL

"Ron Coderre" wrote:

Try this:

For a string in A1

B1: =IF(SUM(COUNTIF(A1,{"now *","* now","* now *"})),"Yes","No")

That formula behave correctly in the below situations:
PHRASE TO TEST RETURNED VALUE
the now look Yes
now is the time Yes
the time is now Yes
to know me No
it is known to me No
a nowhere man No

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"vito" wrote:

i want to know whether a string is in a cell, built-in search function,
using formula of vlookup and find do not help.

e.g. i'm searching the word "now"

the text "i want to know whether a string is in a cell, built-in search
function, using formula of vlookup and find do not help." will be returned
as a match while it is not i want.

could anybody tell me what i can do? thanks in advance.