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

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

Eric wrote:

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
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

=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.
"Eric" wrote in message
...
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

One mo

=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


"Eric" wrote in message
...
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

Ooops!

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


--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
=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)

Eric wrote:

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

Thank everyone very much for suggestions
Eric

"T. Valko" wrote:

One mo

=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


"Eric" wrote in message
...
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

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" wrote:

One mo

=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


"Eric" wrote in message
...
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

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


"Eric" wrote in message
...
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" wrote:

One mo

=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


"Eric" wrote in message
...
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

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


"T. Valko" wrote in message
...
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


"Eric" wrote in message
...
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" wrote:

One mo

=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


"Eric" wrote in message
...
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

On Wed, 19 Mar 2008 22:01:01 -0700, Eric
wrote:

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

On Wed, 19 Mar 2008 22:01:01 -0700, Eric
wrote:

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