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
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet39​

Worksheet Formulas Cell Formula C1 =IF(ISNUMBER(SEARCH("Vice President",A1)),"Vice President",IF(ISNUMBER(SEARCH("President",A1)),"President",IF(ISNUMBER(SEARCH("Secretary",A1)),"Secretary",""))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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