a exact string search inquiry

V

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

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
 
V

vito

Peo Sjoblom said:
=IF(ISNUMBER(SEARCH("now",A1)),"Exists","Do not exist")

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

--

Regards,

Peo Sjoblom
Click to expand...


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
 
R

Ron Coderre

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
 
B

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
 
V

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

Ron Coderre

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
 
A

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
 
W

wal50

Thanks. This was what I needed too.
WAL

Ron Coderre said:
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
Click to expand...
 
Top