Share via

Simple problem with Excel formula

Anonymous
2025-05-19T02:57:28+00:00

I've used Excel for years, but obviously not picked up something important. Can someone tell me what is wrong with the following formulas in columns C and D? I'm aiming to extract name titles from names. I expect column C to read 'Mr" in the case of Mr John Smith, and column D to read 'TRUE' in all but Lou Alaimo.

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-19T08:14:51+00:00

    Image

    Hi,

    As far as I can see you have found your answer but anyway if you are interested see the solution below. In column C starting from C2 I have placed different titles that can have a name (of course you can expand or narrow this list according to your needs).

    If you want to extract only the titles you can use this formula:

    =IFERROR(TRIM(SUBSTITUTE(A2:.A20, TEXTAFTER(A2:.A20, C2:.C20), "")), "")

    If you want to extract the titles in one column and the names in another column then you can use this formula.

    =LET(n, A2:.A20, tr, TRIM(TEXTAFTER(n, C2:.C20)), HSTACK(IFERROR(TRIM(SUBSTITUTE(n, tr, "")) & ".", ""), IFERROR(tr, n)))

    To apply these formulas require that you use Excel 365.

    Hope this helps.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. riny 20,870 Reputation points Volunteer Moderator
    2025-05-19T04:50:20+00:00

    Your formula in B2 returns "Ms ". Note the extra space!! That's not the same as "Ms" and therefore your formula in C returns "". And the formula in D will always return FALSE as none of the results will ever be "Dr", "Mr" or "Ms" as they all have the trailing space.

    In B use =LEFT(A2, FIND(" ", A") - 1)

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2025-05-19T06:40:54+00:00

    Thank you very much Riny -- I should have thought of that. You've restored my sanity.

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more