Finding specific text in a cell with inexact results

[bob33]

I've a column of 700+ names with professional title (Chair, Professor, Associate Professor, etc...).
I'm trying to find the occurrences of these titles in one cell, then placing an X in another cell based on formulas that follow.

Formula for Professor (in one column):

Please Login or Register  to view this content.

Formula for Assistant Professor (in another column):

Please Login or Register  to view this content.

If I search a cell containing:
Associate Professor

both formulas place an X. So the first is finding Professor and the second is finding Assistant Professor.

How can I change the formula to get discriminate results?

[dredwolf]

If the cell only contains 1 title, you could use something like this:
=IF($W7="Professor","X","-"), this would assume the titles didn't have leading/trailing spaces
=IF(TRIM($W7)="Professor","X","-") if in doubt
if the cell is more complex, would need to see some example of the data

Hope this helps

[bob33]

Dredwolf,
I've attached a sample workbook with data for you to see what I have to work with.
Thanks,
Bob

[dredwolf]

Or maybe:
=IF(ISERR(FIND("Professor",$W7)),"-",IF(ISERR(FIND("Assistant",$W7)),"X","-")) 'for Professor
=IF(ISERR(FIND("Professor",$W7)),"-",IF(ISERR(FIND("Assistant",$W7)),"-","X")) 'for Assistant Professor

Hope this helps

Edit, just seen the workbook, give me a bit

[dredwolf]

See this:Bob33.sol1.xlsx

The 2 formulas from last post seem to do the trick

Edit-
This would assume there are no assistant chairs who are aslo professor's though...

[bob33]

Dredwolf,
Nicely done! I hadn't thought of nesting them that way.
Thank you,
Bob

[dredwolf]

Modified:Bob33.sol1A.xlsx

New Formulas:

Please Login or Register  to view this content.

for professor

Please Login or Register  to view this content.

for assistant professor
(note the ranges for the comparison are for the sample)

Hope this helps

[dredwolf]

You are welcome
I would use the second set though, just in case

[Tank997]

You could also try this since you are using 2007 it will work as long as "Professor" and/or "Assistant" are the first words in the text.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[dredwolf]

@ Tank, as per the sample, they can be anywhere in a large amount of multiple titles, so it needs to check if the other is there (at least in the first case..)

[Tank997]

dredwolf, you are correct. I was looking at the sample file and saw the titles as the first word in the first 6 lines and did not look further. I also did not see your post before I posted. Sorry for the confusion.

[dredwolf]

No Problem, just thought I'd point it out is all

[bob33]

Thanks! The revisions are working.
I did notice that these are case-sensitive; is there a formula where it would find both Professor and professor?

[dredwolf]

Change the 'FIND' to 'SEARCH', Search is not case sensitive

[bob33]

Thanks Dred!

[dredwolf]

you are welcome