Prev: formatted cells
Next: Date question
From: Vibeke on 16 Apr 2010 15:49
Hi, I have four columns of data (C-F) I need to search for a particular word.
The data is addresses, i.e. it combines text and numbers and the word I'm
searching for can appear anywhere within it (or not at all). The function I
require will return 1 if it does and 0 if it does not. Have tried various
iterations of if\search\match and so on to no avail - perhaps because I'm
searching a range of columns rather than a specific cell? I'd appreciate your
suggestions. Regards,
From: T. Valko on 16 Apr 2010 16:16
Maybe something like this...

=COUNT(MATCH("*word*",C1:F1,0))

--
Biff
Microsoft Excel MVP


"Vibeke" <Vibeke(a)discussions.microsoft.com> wrote in message
news:31C30A9F-CE35-4B31-937C-114C5C31107E(a)microsoft.com...
> Hi, I have four columns of data (C-F) I need to search for a particular
> word.
> The data is addresses, i.e. it combines text and numbers and the word I'm
> searching for can appear anywhere within it (or not at all). The function
> I
> require will return 1 if it does and 0 if it does not. Have tried various
> iterations of if\search\match and so on to no avail - perhaps because I'm
> searching a range of columns rather than a specific cell? I'd appreciate
> your
> suggestions. Regards,


From: Steve Dunn on 19 Apr 2010 04:01
or,

=--(countif(c1:f1,"*word*")>0)

but there is a possible problem. If the word you are searching for appears
within another word. e.g. "forward" contains "for", "or", and "ward", so if
your cells contained "forward" and you were looking for one of those words,
they would be found even if they weren't actually there. A slight variation
would be:

=--(countif(c1:f1,"* word *")>0)

but you would then have a situation where words at the beginning or end of a
string, or words with punctuation beside them, would not be found. So the
answer to this one could be quite a bit more complex than you would think,
is that why you have ignored the answers given to your earlier post?



"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:elqokGa3KHA.5820(a)TK2MSFTNGP06.phx.gbl...
> Maybe something like this...
>
> =COUNT(MATCH("*word*",C1:F1,0))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Vibeke" <Vibeke(a)discussions.microsoft.com> wrote in message
> news:31C30A9F-CE35-4B31-937C-114C5C31107E(a)microsoft.com...
>> Hi, I have four columns of data (C-F) I need to search for a particular
>> word.
>> The data is addresses, i.e. it combines text and numbers and the word I'm
>> searching for can appear anywhere within it (or not at all). The function
>> I
>> require will return 1 if it does and 0 if it does not. Have tried various
>> iterations of if\search\match and so on to no avail - perhaps because I'm
>> searching a range of columns rather than a specific cell? I'd appreciate
>> your
>> suggestions. Regards,
>
>

 | 
Pages: 1
Prev: formatted cells
Next: Date question