Extracting text from cells

[Sprong]

Hi,

I'm trying to develop some formulas for extracting text from other cells.

The raw data that I receive ALWAYS comes in the following format:
(last name) (add. last name [theoretically repeated as many as necessary]) (credentials), (first name) (middle name [theoretically repeated as many as necessary])

I need help with two formulas: one for extracting a person's credentials, and one for extracting all last name(s). I'm kind of new at this, so sorry if the answer is obvious.

1. Extracting the Credentials
Need: formula that grabs the single word just before a comma.
Current formula: =MID(LEFT(D2,FIND(",",D2)-1),FIND(" ",D2)+1,LEN(D2))
Why it doesn't work: grabs part of a person's last name when multiple last names are given
*Note: a persons credentials are only ever one word (ex. PhD, MD, etc.)

2. Extracting the full last name
Need: formula that grabs all words excluding the single word just before the comma.
Current formula: =LEFT(D2,FIND(" ",D2)-1)
Why it doesn't work: does not capture all last names

I've created a dummy workbook so you all can see the problem for yourselves.

Thanks so much!

[AliGW]

The attachment is invalid - please try again.

[Sprong]

I tried uploading it again, is it working now?

[AliGW]

Yes, thanks - taking a look.

[AliGW]

Last name: =LEFT(SUBSTITUTE(D2,TRIM(RIGHT(SUBSTITUTE(LEFT(D2,FIND(",",D2)-1)," ",REPT(" ",99)),98)),""),FIND(",",SUBSTITUTE(D2,TRIM(RIGHT(SUBSTITUTE(LEFT(D2,FIND(",",D2)-1)," ",REPT(" ",99)),98)),""))-1)

Title: =TRIM(RIGHT(SUBSTITUTE(LEFT(D2,FIND(",",D2)-1)," ",REPT(" ",99)),98))

[Sprong]

Awesome! Thanks so much.

[AliGW]

No worries.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.