Text Formula
 

I have a column of about 500 cells that contains different strings of words
(.e. "product download", "product video", "product documentation"). I would
like to create a formula such that Excel will tell me if the the string
contains "download", "video", or "documentation" and assign a value to each
of those (i.e. if the cell contains document then Excel would assign a "d" or
"1" and if it contains "video" it will assign a "v" or "2"). Thus far I can
only use the = sign in formulas (i.e. IF=) and this allows me to consider the
all the content in the cell and not an option for "contains either." Also,
the conditional formatting does not seem to work as I need nested formulas
and do not want a color.

Thanks in advance

Text Formula
 

This formula should work (assuming I'm understanding your problem). So, if
your data looks like this:

Column A
"product video"
"product download"
"something else"
"product demonstration"

you should be able to add this formula in column B:

=IF(NOT(ISERROR(FIND("download",A1))),"d",IF(NOT(I SERROR(FIND("video",A1))),"v",IF(NOT(ISERROR(FIND( "documentation",A1))),"doc","")))

Where you see "d", "v", and "doc" -- that's what the formula returns when it
finds an instance of "download", "video", and "documentation". Change those
to whatever you'd like.

Hope this answers your question!

"Joanne" wrote:

I have a column of about 500 cells that contains different strings of words
(.e. "product download", "product video", "product documentation"). I would
like to create a formula such that Excel will tell me if the the string
contains "download", "video", or "documentation" and assign a value to each
of those (i.e. if the cell contains document then Excel would assign a "d" or
"1" and if it contains "video" it will assign a "v" or "2"). Thus far I can
only use the = sign in formulas (i.e. IF=) and this allows me to consider the
all the content in the cell and not an option for "contains either." Also,
the conditional formatting does not seem to work as I need nested formulas
and do not want a color.

Thanks in advance

Text Formula
 

One way:

=IF(COUNT(SEARCH("download",A1)),1,IF(COUNT(SEARCH ("video",A1)),2,IF(COUNT(SEARCH("documentation",A1 )),3,"none")))

Another one:

Create a table like this:

.............G................H.....
1...download...........1
2...video..................2
3...documentation....3

=IF(ISNA(LOOKUP(2,1/SEARCH(G1:G3,A1),H1:H3)),"none",LOOKUP(2,1/SEARCH(G1:G3,A1),H1:H3))


--
Biff
Microsoft Excel MVP


"Joanne" <Joanne @discussions.microsoft.com wrote in message
...
I have a column of about 500 cells that contains different strings of words
(.e. "product download", "product video", "product documentation"). I
would
like to create a formula such that Excel will tell me if the the string
contains "download", "video", or "documentation" and assign a value to
each
of those (i.e. if the cell contains document then Excel would assign a "d"
or
"1" and if it contains "video" it will assign a "v" or "2"). Thus far I
can
only use the = sign in formulas (i.e. IF=) and this allows me to consider
the
all the content in the cell and not an option for "contains either." Also,
the conditional formatting does not seem to work as I need nested formulas
and do not want a color.

Thanks in advance

Text Formula
 

If different "key words" can never appear together in the same cell, then
this will work (although it will produce a 0 if none of the words are in the
cell)...

=COUNTIF(A1,"*download*")+2*COUNTIF(A1,"*video*")+ 3*COUNTIF(A1,"*documentation*")

Rick


"Joanne" <Joanne @discussions.microsoft.com wrote in message
...
I have a column of about 500 cells that contains different strings of words
(.e. "product download", "product video", "product documentation"). I
would
like to create a formula such that Excel will tell me if the the string
contains "download", "video", or "documentation" and assign a value to
each
of those (i.e. if the cell contains document then Excel would assign a "d"
or
"1" and if it contains "video" it will assign a "v" or "2"). Thus far I
can
only use the = sign in formulas (i.e. IF=) and this allows me to consider
the
all the content in the cell and not an option for "contains either." Also,
the conditional formatting does not seem to work as I need nested formulas
and do not want a color.

Thanks in advance