If a certain word in a sentence, then write that certain word in different cell

[jbirenz]

Hi all - Am trying to figure out how to to this. I have a list of job titles (column A) - I want to search each cell for a specific word, and then return that word in column C. Please help!!

So if cell A2 has the word director in it, then I want cell C2 to say director, if cell A2 has the word manager in it, then I want cell C2 to say manager)....if it doesn't have either of those, then I want it to return 0.

Column A






Account Director
Account Manager
SVP, Digital
Marketing Executive


I think this formula would work...but I need to add multiple searches.

=IF(ISNUMBER(SEARCH("director",A2))"director")

 

[Joe4]

Welcome to the Board!

Exactly how many different words might you be looking for?
You mentioned "director" and "manager", but how long is the list really?
How many we need to look for may influence what kind of solution we come up with.

If it just two, this would work:

=IF(ISNUMBER(SEARCH("director",A2)),"director",IF(ISNUMBER(SEARCH("manager",A2)),"manager",0))

 

[jbirenz]

Thanks for the welcome and for your help!

The column with the job titles to search is 399 cells

The list of words to look for is a total of 6:
Individual Contributor
Manager
Director
Vice President
SVP
Executive

 

[Weazel]

It would probably be more efficient if you used a lookup table....

=LOOKUP(9.99E+307,SEARCH($G$1:$G$6,A1),$G$1:$G$6) would assume your list was in column G.

if a lookup table isn't possible than something like...

=LOOKUP(9.99e+307,SEARCH({"Director";"Executive";"Individual Contributor";"Manager";"SVP";"Vice President"},A1),{"Director";"Executive";"Individual Contributor";"Manager";"SVP";"Vice President"})

which, IMO is kind of long.

Also you'd want to make sure your list is sorted ascending

 

[jbirenz]

It would probably be more efficient if you used a lookup table....

=LOOKUP(9.99E+307,SEARCH($G$1:$G$6,A1),$G$1:$G$6) would assume your list was in column G.

if a lookup table isn't possible than something like...

=LOOKUP(9.99e+307,SEARCH({"Director";"Executive";"Individual Contributor";"Manager";"SVP";"Vice President"},A1),{"Director";"Executive";"Individual Contributor";"Manager";"SVP";"Vice President"})

which, IMO is kind of long.

Also you'd want to make sure your list is sorted ascending

THANK YOU! That formula works perfectly!!!