Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Link to text and return text into a formula? | Excel Worksheet Functions | |||
Excel:Get concatenated text to be recognised as formula not text? | Excel Discussion (Misc queries) | |||
Formula to count text and alert me if a text appears more than twi | Excel Discussion (Misc queries) | |||
match cell text with text in formula | Excel Worksheet Functions |