0

I have names that are listed with salutations (ex. Mr. Mrs. Dr.). I am struggling with a formula that will search for the existence of those text strings, and, if one exists, return the salutation.

So, I would like the formula to look at "Dr. Nancy Briggs," and return "Dr."

Versions I have been trying include: =IF(ISNUMBER(SEARCH({"Mr.","Mrs.","Dr."},C13)),LEFT(C13,FIND(" ",C13,1)-1),"")

=IF(OR(ISNUMBER(SEARCH("Mr.",C24)),ISNUMBER(SEARCH("Mrs.",C24)),ISNUMBER(SEARCH("Dr.",C24))),LEFT(C24,FIND(" ",C24,1)-1),"")

  • the salutations are always at the front, so I can extract them using the LEFT function. But, ideally, I would like to extract them from anywhere.

The second formula works, but is clunky. Your help is so very much appreciated!

Improve this question

1 Answer 1

Reset to default
1

You can use a nested IF:

 =IF(ISNUMBER(SEARCH("Mr.")),"Mr.",IF(ISNUMBER(SEARCH("Mrs.")),"Mrs.",IF(ISNUMBER(SEARCH("Dr.")),"Dr.","No Salutation")))

If you have OFFICE 365:

You can use CONCAT as an Array formula:

=CONCAT(IF(ISNUMBER(SEARCH({"Mr.","Mrs.","Dr."},C13)),{"Mr.","Mrs.","Dr."},"")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter

Or IFS()

=IFS(ISNUMBER(SEARCH("Mr.")),"Mr.",ISNUMBER(SEARCH("Mrs.")),"Mrs.",ISNUMBER(SEARCH("Dr.")),"Dr.",TRUE,"No Salutation")

The only real changes is that I am not trying to pull the return from the string as you only want the actual salutation.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much! The second one is exactly what I'm looking for!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.