Syntax for 'search' in Horizontal range? XL2007
 

I have a range of dirty data I'm trying to clean (data showing up in
different columns, because the original input was not put in the same fields
systematically).

I have a keyword that may show up inside any of the fields, and I need to
identify when that occurs so I can hand-verify those records. Example: Search
(case insensitive) for "Cat" in a range that includes:

(A) (B) (C) Result
(1) dog Door Green False/blank/whatever
(2) Red Window bobcat True/Yes/whatever

The likely candidate seemed to be the SEARCH function in XL2007. With some
minor testing, it appears that I can find my keyword when I use SEARCH with a
single cell (not helpful) or with a vertical range regardless of whether the
target cell is at the beginning of the range.

However, as soon as I try to use SEARCH with a horizontal range, I get a
#VALUE calculation error.

(1) Is this expected behavior, that SEARCH returns a result with vertical
ranges but not horizontal? (note: the output is still where the search
string is located, not which cell it is found in, but that's ok because I
still know it was found /somewhere/)

(2) Is there a better way to determine whether a horizontal range contains a
pre-determined string fragment? Note: the cell will contain more than just
the fragment, I'm looking for anywhere a cell *contains* my target string.

Many thanks,
Keith

Syntax for 'search' in Horizontal range? XL2007
 

=countif(a1:c1,"*Cat*")0
or if you put Cat in a different cell:
=countif(a1:c1,"*"&x99&"*")0




ker_01 wrote:

I have a range of dirty data I'm trying to clean (data showing up in
different columns, because the original input was not put in the same fields
systematically).

I have a keyword that may show up inside any of the fields, and I need to
identify when that occurs so I can hand-verify those records. Example: Search
(case insensitive) for "Cat" in a range that includes:

(A) (B) (C) Result
(1) dog Door Green False/blank/whatever
(2) Red Window bobcat True/Yes/whatever

The likely candidate seemed to be the SEARCH function in XL2007. With some
minor testing, it appears that I can find my keyword when I use SEARCH with a
single cell (not helpful) or with a vertical range regardless of whether the
target cell is at the beginning of the range.

However, as soon as I try to use SEARCH with a horizontal range, I get a
#VALUE calculation error.

(1) Is this expected behavior, that SEARCH returns a result with vertical
ranges but not horizontal? (note: the output is still where the search
string is located, not which cell it is found in, but that's ok because I
still know it was found /somewhere/)

(2) Is there a better way to determine whether a horizontal range contains a
pre-determined string fragment? Note: the cell will contain more than just
the fragment, I'm looking for anywhere a cell *contains* my target string.

Many thanks,
Keith

--

Dave Peterson

Syntax for 'search' in Horizontal range? XL2007
 

Thank you!

"Dave Peterson" wrote:

=countif(a1:c1,"*Cat*")0
or if you put Cat in a different cell:
=countif(a1:c1,"*"&x99&"*")0




ker_01 wrote:

I have a range of dirty data I'm trying to clean (data showing up in
different columns, because the original input was not put in the same fields
systematically).

I have a keyword that may show up inside any of the fields, and I need to
identify when that occurs so I can hand-verify those records. Example: Search
(case insensitive) for "Cat" in a range that includes:

(A) (B) (C) Result
(1) dog Door Green False/blank/whatever
(2) Red Window bobcat True/Yes/whatever

The likely candidate seemed to be the SEARCH function in XL2007. With some
minor testing, it appears that I can find my keyword when I use SEARCH with a
single cell (not helpful) or with a vertical range regardless of whether the
target cell is at the beginning of the range.

However, as soon as I try to use SEARCH with a horizontal range, I get a
#VALUE calculation error.

(1) Is this expected behavior, that SEARCH returns a result with vertical
ranges but not horizontal? (note: the output is still where the search
string is located, not which cell it is found in, but that's ok because I
still know it was found /somewhere/)

(2) Is there a better way to determine whether a horizontal range contains a
pre-determined string fragment? Note: the cell will contain more than just
the fragment, I'm looking for anywhere a cell *contains* my target string.

Many thanks,
Keith

--

Dave Peterson