K
ker_01
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
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