Is there an Include Function?

  • Thread starter Thread starter Extraction
  • Start date Start date
E

Extraction

I downloaded a set of data that has many data items within
a single cell. What I would like to do is have Excel
search the cell for a value (e.g., I want to create an if-
then formula that returns 1 if "west" is included in a
cell and 0 otherwise; such that "keywest" would return 1
and keyeast would return 0).

Is there a way to do this?
 
Extraction wrote...
I downloaded a set of data that has many data items within a
single cell. What I would like to do is have Excel search the cell
for a value (e.g., I want to create an if-then formula that returns
1 if "west" is included in a cell and 0 otherwise; such that
"keywest" would return 1 and keyeast would return 0).
Click to expand...

If the cell you wanted to check were A1, you could try

=COUNTIF(A1,"*west*"
 
Myrna Larson wrote...
Take a look at the SEARCH and FIND worksheet functions.
Click to expand...
...

Followed by looking at the ISERR and ISERROR worksheet functions. OP'
task can't be done with *just* SEARCH or FIND
 
I didn't say that it could, did I? I'm not sure WHAT he has in those cells to
be searched, are you?
 
Myrna Larson wrote...
I didn't say that it could, did I? I'm not sure WHAT he has in
those cells to be searched, are you?
Click to expand...
...

From the OP:

"(e.g., I want to create an if-then formula that returns 1 if "west"
is included in a cell and 0 otherwise; such that "keywest" would
return 1 and keyeast would return 0)."

Neither of us may know what the OP has in his/her cells, but only on
of us seems to be addressing what FIND and SEARCH return when th
sought substring doesn't occur at all in the string searched. They'r
not as nice as VBA's InStr. They return #VALUE! So if the OP wants
returned if there were no instance of substring X in string Y, neithe
FIND nor SEARCH would be sufficient.

Aside from using COUNTIF, which only works when Y is a cell, there'
also --(SUBSTITUTE(Y,X,"")<>Y). There's a lot to be said for singl
function call solutions. There's also something to be said for multipl
function call solutions if they provide greater clarity, but in thos
cases all the necessary functions should be discussed
 
Back
Top