a exact string search inquiry

[vito]

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]

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

=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

 

[Biff]

Hi!

Try this:

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

Biff

 

[Guest]

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

 

[Biff]

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

 

[vito]

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?

 

[Guest]

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

 

[Ardus Petus]

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

 

[Guest]

Thanks. This was what I needed too.
WAL

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