If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function to seacrh for a word across multiple columns
Hi, I have four columns of data (C-F) I need to search for a particular word.
The data is addresses, i.e. it combines text and numbers and the word I'm searching for can appear anywhere within it (or not at all). The function I require will return 1 if it does and 0 if it does not. Have tried various iterations of if\search\match and so on to no avail - perhaps because I'm searching a range of columns rather than a specific cell? I'd appreciate your suggestions. Regards, |
#2
|
|||
|
|||
Function to seacrh for a word across multiple columns
Maybe something like this...
=COUNT(MATCH("*word*",C1:F1,0)) -- Biff Microsoft Excel MVP "Vibeke" wrote in message ... Hi, I have four columns of data (C-F) I need to search for a particular word. The data is addresses, i.e. it combines text and numbers and the word I'm searching for can appear anywhere within it (or not at all). The function I require will return 1 if it does and 0 if it does not. Have tried various iterations of if\search\match and so on to no avail - perhaps because I'm searching a range of columns rather than a specific cell? I'd appreciate your suggestions. Regards, |
#3
|
|||
|
|||
Function to seacrh for a word across multiple columns
or,
=--(countif(c1:f1,"*word*")0) but there is a possible problem. If the word you are searching for appears within another word. e.g. "forward" contains "for", "or", and "ward", so if your cells contained "forward" and you were looking for one of those words, they would be found even if they weren't actually there. A slight variation would be: =--(countif(c1:f1,"* word *")0) but you would then have a situation where words at the beginning or end of a string, or words with punctuation beside them, would not be found. So the answer to this one could be quite a bit more complex than you would think, is that why you have ignored the answers given to your earlier post? "T. Valko" wrote in message ... Maybe something like this... =COUNT(MATCH("*word*",C1:F1,0)) -- Biff Microsoft Excel MVP "Vibeke" wrote in message ... Hi, I have four columns of data (C-F) I need to search for a particular word. The data is addresses, i.e. it combines text and numbers and the word I'm searching for can appear anywhere within it (or not at all). The function I require will return 1 if it does and 0 if it does not. Have tried various iterations of if\search\match and so on to no avail - perhaps because I'm searching a range of columns rather than a specific cell? I'd appreciate your suggestions. Regards, |
Thread Tools | |
Display Modes | |
|
|