Prev: Finding NEXT to last, non zero numeric value in a row
Next: Sum of retuned valus as digits
From: Vibeke on 16 Apr 2010 03:10
I have 4 columns (C to F) I need to search row by row for a particular word.
If the word is found in a row, I need to return 1 in Column G or 0 if not
found. I've tried various iterations of IF\SEARCH\MATCH and so on. The four
columns contain address lines, i.e. a mixture of text and numbers and the
word can appear in any of the columns, at any place in the string. I'd prefer
if it weren't case sensitive but it's not imperative. Many thanks in
anticipation!
From: Jacob Skaria on 16 Apr 2010 03:22
Try the below

=IF(COUNTIF(C4:F4,"*findword*"),1,0)

'with the query word in cell A1
=IF(COUNTIF(C4:F4,"*" & A1 & "*"),1,0)

--
Jacob (MVP - Excel)


"Vibeke" wrote:

> I have 4 columns (C to F) I need to search row by row for a particular word.
> If the word is found in a row, I need to return 1 in Column G or 0 if not
> found. I've tried various iterations of IF\SEARCH\MATCH and so on. The four
> columns contain address lines, i.e. a mixture of text and numbers and the
> word can appear in any of the columns, at any place in the string. I'd prefer
> if it weren't case sensitive but it's not imperative. Many thanks in
> anticipation!
From: Jarek Kujawa on 16 Apr 2010 05:09
another way:
A1="word"

=IF(ISERROR(FIND(A1,C1&D1&E1&F1)),,1)

HIH

On 16 Kwi, 09:10, Vibeke <Vib...(a)discussions.microsoft.com> wrote:
> I have 4 columns (C to F) I need to search row by row for a particular word.
> If the word is found in a row, I need to return 1 in Column G or 0 if not
> found. I've tried various iterations of IF\SEARCH\MATCH and so on. The four
> columns contain address lines, i.e. a mixture of text and numbers and the
> word can appear in any of the columns, at any place in the string. I'd prefer
> if it weren't case sensitive but it's not imperative. Many thanks in
> anticipation!

 | 
Pages: 1
Prev: Finding NEXT to last, non zero numeric value in a row
Next: Sum of retuned valus as digits