Extract Text After The Last Instance Of A Specific Character

Looking for formula to extract characters after the last instance of a specific character.

Formula works but does not correctly pull this instance 1999 - 286082102 - Electronics For Imaging, Inc. - Gassee, Jean-Louis. See the two examples below.



Desired Outcome = Johnson, William P.
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2)))
1999 - 189873102 - Coachmen Industries, Inc. - Johnson, William P.

Formula does not work for this:
Outcome:Louis
Desired Outcome = Gassee, Jean-Louis
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2)))
1999 - 286082102 - Electronics For Imaging, Inc. - Gassee, Jean-Louis

Thanks

There is no surprise.
Use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

BMV:

=TRIM(RIGHT(SUBSTITUTE(A3," - ",REPT(" ",LEN(A3))),LEN(A3)))
OR
=TRIM(RIGHT(SUBSTITUTE(A3," -",REPT(" ",LEN(A3))),LEN(A3)))

Works for:
1999 - 189873102 - Coachmen Industries, Inc. - Johnson, William P.
1999 - 286082102 - Electronics For Imaging, Inc. - Gassee, Jean-Louis

but not this. Just noticed it now. Please help fix.
BMV: 1999 - 423074103 - H. J. Heinz Company - Johnson, William R (W. R.) - (Bill)

Desired results: Johnson, William R (W. R.) - (Bill)

Originally Posted by bjnockle

but not this. Just noticed it now. Please help fix.
BMV: 1999 - 423074103 - H. J. Heinz Company - Johnson, William R (W. R.) - (Bill)

Desired results: Johnson, William R (W. R.) - (Bill)

Your thread title said you wanted the text after the last occurrence of a specific character which you have indicated is a dash. Obviously, that would be "(Bill)" for your example. If you want the full name too, then you need to tell us how we are to recognize to ignore the last dash in your example. Will the way to recognize it is if the text after the last dash (ignoring spaces) is surrounded by parentheses, then use the previous dash as the delimiter? If so, try this...

=SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"- (","||"),"-",REPT(" ",200)),200),200)),"||","- (")

BMV: Excellent solution. Thanks a lot.

Bo_Ry: Excellent solution. Thanks a lot.

bluesky63 : Outstanding solution. Thanks a lot.

Rick: Outstanding solution. Thanks a lot.