How to search for text?

[Eric]

Does anyone have any suggestions on how to search for text?
For example, Apple (93) in cell A1,
I would like to search for any text including Apple in cell A1,
It should return True in cell B1, because the text Apple is included in cell
A1.
Does anyone have any suggestions?
Thank you very much
Eric

 

[Dave Peterson]

=isnumber(search("apple",a1)
or
=isnumber(find("Apple",a1))

=Find() is case sensitive.
=Search() is not.

Another:
=countif(a1,"*apple*")>0
(also not case sensitive)

 

[Max]

In B1: =ISNUMBER(SEARCH("Apple",A1))
would be one way to check that the text: Apple is in A1 or not

Use the stricter FIND if you need it to be a case sensitive search

 

[Alan]

=ISNUMBER(SEARCH("Apple",A1,1))
This isn't case sensitive, if you want it case sensitive,
=ISNUMBER(FIND("Apple",A1,1))
Note that this will return TRUE for Apples, Grapple, Grappled etc, ie any
word that contains the five letter sequence 'apple'
Regards,
Alan.

 

[T. Valko]

One more:

=COUNTIF(A1,"*apple*")>0

NB: this type of fuzzy matching is vulnerable to false positives. For
example, the above will match candyapple or Snapple.

 

[T. Valko]

Ooops!

I didn't scroll down far enough to see that you had already suggested
"Another:".

 

[Eric]

Thank everyone very much for suggestions
Eric

One more:

=COUNTIF(A1,"*apple*")>0

NB: this type of fuzzy matching is vulnerable to false positives. For
example, the above will match candyapple or Snapple.

 

[Eric]

For fuzzy matching, the countif function is working when the source file is
open, such as
=COUNTIF('[Source.xls]sheet'!A1,"apple*")>0
but, the countif function is not working when the source file is closed,
it returns #VALUE!

I have tried the function search and find, both functions are working when
the source file is closed, but it seems to me that both do not work for fuzzy
matching.
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

 

[T. Valko]

It depends on what you're searching for any what the strings look like.

A1 = Snapple (22)

=COUNT(SEARCH("apple",A1))>0 = TRUE

=COUNT(SEARCH(" apple "," "&A1&" "))>0 = FALSE

In the second example we're looking for the *explicit* word "apple". We do
that by "padding" each end of the word with spaces and concatenating each
end of the string with spaces. However, this can also fail if special
characters might be present. Like this:

A1 = Apple, (22)

=COUNT(SEARCH(" apple "," "&A1&" "))>0 = FALSE

So, it all depends on what you're looking for and what the strings look
like.

A regular expression UDF might be needed when special characters are present
but I'm not very familiar with regex. Ron Rosenfeld is the resident expert
on regex.


--
Biff
Microsoft Excel MVP

For fuzzy matching, the countif function is working when the source file
is
open, such as
=COUNTIF('[Source.xls]sheet'!A1,"apple*")>0
but, the countif function is not working when the source file is closed,
it returns #VALUE!

I have tried the function search and find, both functions are working when
the source file is closed, but it seems to me that both do not work for
fuzzy
matching.
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

One more:

=COUNTIF(A1,"*apple*")>0

NB: this type of fuzzy matching is vulnerable to false positives. For
example, the above will match candyapple or Snapple.

 

[T. Valko]

I have tried the function search and find...but it seems to me that both do

not work for fuzzy matching.

A less convoluted explanation is that SEARCH and FIND do almost the same
thing but *not exactly* the same thing. FIND is case sensitive while SEARCH
is not. SEARCH will accept wildcards while FIND will not.

A1 = Apple

=SEARCH("apple",A1) = 1

=FIND("apple",A1) = #VALUE!


--
Biff
Microsoft Excel MVP

It depends on what you're searching for any what the strings look like.

A1 = Snapple (22)

=COUNT(SEARCH("apple",A1))>0 = TRUE

=COUNT(SEARCH(" apple "," "&A1&" "))>0 = FALSE

In the second example we're looking for the *explicit* word "apple". We do
that by "padding" each end of the word with spaces and concatenating each
end of the string with spaces. However, this can also fail if special
characters might be present. Like this:

A1 = Apple, (22)

=COUNT(SEARCH(" apple "," "&A1&" "))>0 = FALSE

So, it all depends on what you're looking for and what the strings look
like.

A regular expression UDF might be needed when special characters are
present but I'm not very familiar with regex. Ron Rosenfeld is the
resident expert on regex.


--
Biff
Microsoft Excel MVP

For fuzzy matching, the countif function is working when the source file
is
open, such as
=COUNTIF('[Source.xls]sheet'!A1,"apple*")>0
but, the countif function is not working when the source file is closed,
it returns #VALUE!

I have tried the function search and find, both functions are working
when
the source file is closed, but it seems to me that both do not work for
fuzzy
matching.
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

One more:

=COUNTIF(A1,"*apple*")>0

NB: this type of fuzzy matching is vulnerable to false positives. For
example, the above will match candyapple or Snapple.


--
Biff
Microsoft Excel MVP


Does anyone have any suggestions on how to search for text?
For example, Apple (93) in cell A1,
I would like to search for any text including Apple in cell A1,
It should return True in cell B1, because the text Apple is included
in
cell
A1.
Does anyone have any suggestions?
Thank you very much
Eric

 

[Ron Rosenfeld]

I have tried the function search and find, both functions are working when
the source file is closed, but it seems to me that both do not work for fuzzy
matching.
Does anyone have any suggestions?

If by "fuzzy matching" you mean matching "apple" when it exists anyplace within
the string, my suggestion is that you read about and try out the SEARCH and
FIND functions. Be sure in your reading to consider the slight difference
between the two.
--ron

 

[Ron Rosenfeld]

I have tried the function search and find, both functions are working when
the source file is closed, but it seems to me that both do not work for fuzzy
matching.

My last post was unduly harsh.

What result do you get, and what formula are you using, when you try the FIND
or SEARCH functions and they "do not work for fuzzy matching".
--ron