If Cell contains x, return x, if it contains y, return y, or if it contains z, return z.

[torourke17]

I have thousands of cells with text containing various descriptions. I'm trying to search the text and tell me if the description says x, y, or z and to return which one it says. The descriptions are all over the board, but contain certain key words that I need to categorize. Example Below:

Data:
Cell A1 - "President Services May '18"
Cell A2 - "Services - Vice President - June '17"
Cell A3 - "Services April '16 - Secretary"
Cell A4 - "President March '18"
Cell A5 - "Secretary Services - January '18"
Cell A6 - "Secretary Services - May '18"
Cell A7 - "Vice President Services -March '18"

Formula would tell me if the cell is President, Vice President, or Secretary.
Cell B1 - President
Cell B2 - Vice President
Cell B3 - Secretary
etc.
etc.
etc.

 

[JonXL]

=IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=2,"Vice President",IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=1,"President","Secretary"))

* Assumes all cells in A contain President, Vice President, or Secretary (if there are any that don't contain at least one of those three, then use:

=IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=2,"Vice President",IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=1,"President",IF(COUNTIF(A1,"*Secretary*"),"Secretary","")))

 

[jtakw]

Hi,

Here's another way:

Book1
	A	B	C
1	President Services May '18		President
2	Services - Vice President - June '17		Vice President
3	Services April '16 - Secretary		Secretary
4	Staff Meeting May '18
5	President March '18		President
6	Secretary Services - January '18		Secretary
7	Secretary Services - May '18		Secretary
8	Vice President Services -March '18		Vice President
Sheet39

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(SEARCH("Vice President",A1)),"Vice President",IF(ISNUMBER(SEARCH("President",A1)),"President",IF(ISNUMBER(SEARCH("Secretary",A1)),"Secretary","")))

 

[jtakw]

=IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=2,"Vice President",IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=1,"President","Secretary"))

* Assumes all cells in A contain President, Vice President, or Secretary (if there are any that don't contain at least one of those three, then use:

=IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=2,"Vice President",IF(COUNTIF(A1,"*President*")+COUNTIF(A1,"*Vice*")=1,"President",IF(COUNTIF(A1,"*Secretary*"),"Secretary","")))

Hi,

Both formulas will give incorrect results, as there's "vice" in "services".

 

[JonXL]

You are right. I didn't account for all the possibilities given in the example.

Here's a corrected one using the same format:

=IF(COUNTIF(A1,"*Vice President*")=1,"Vice President",IF(COUNTIF(A1,"*President*")=1,"President",IF(COUNTIF(A1,"*Secretary*"),"Secretary","")))

 

[ananpani]

Hi,

Here's another way:

	A	B	C
President Services May '18	President
Services - Vice President - June '17	Vice President
Services April '16 - Secretary	Secretary
Staff Meeting May '18
President March '18	President
Secretary Services - January '18	Secretary
Secretary Services - May '18	Secretary
Vice President Services -March '18	Vice President

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet39​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH("Vice President",A1)),"Vice President",IF(ISNUMBER(SEARCH("President",A1)),"President",IF(ISNUMBER(SEARCH("Secretary",A1)),"Secretary","")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks jtakw, This works perfect if there are handful of search values, but in my case I have a long list of search values in 100s, and the text fields in 1000s, What is the best way to deal this? Thank you