Extracting Last, First, Middle initial, Title into separate columns with a non-consistent submission.

[Flexremmington]

My input column is A. I want to extract Last, First, Middle initial.
My issues are-
Not all names come in the same format. Some have middle initial, most do not. Some have DVM (Dr. of veterinary medicine) behind their last name, most do not.
I need the last names to appear without the DMV in col C, the first name to appear without the middle initial in Col D, and middle initial to appear in E when applicable. The DVM, does not need to appear anywhere, but that postnominal will change depending on what group submits information to me, meaning the name may be submitted as as Del Rio XYZ, Jackson F.

Currently, I am using =LEFT(A2,SEARCH(",",A2,1)-1) in Col C to extract the last name, and =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2))) to extract the first name.

 

[Joe4]

Welcome to the Board!

You could use this for the first name (in D2):

=MID(A2,FIND(",",A2)+2,LEN(A2)-FIND(",",A2)-IF(E2<>"",2,0))

And this for the middle initial (in E2):

=IF(LEFT(RIGHT(A2,2),1)=" ",RIGHT(A2,1),"")

For the last name, you have to get us some "hard and fast" rules we can follow to determine when and what to exclude, since it does not appear to be one specific value.
(In order to program anything, we need to know what we are programming for!)
What are the rules?
Or is there a finite list of possibilities?

 

[Flexremmington]

Welcome to the Board!

You could use this for the first name (in D2):

=MID(A2,FIND(",",A2)+2,LEN(A2)-FIND(",",A2)-IF(E2<>"",2,0))

And this for the middle initial (in E2):

=IF(LEFT(RIGHT(A2,2),1)=" ",RIGHT(A2,1),"")

For the last name, you have to get us some "hard and fast" rules we can follow to determine when and what to exclude, since it does not appear to be one specific value.
(In order to program anything, we need to know what we are programming for!)
What are the rules?
Or is there a finite list of possibilities?

Thanks for the welcome! Past community posts have already proven invaluable as I learn to navigate Excel beyond the most basic level.

The above first name and middle initial advice both worked flawlessly. Thank you!

As far as last name rules- I am in the early stages of this project so I have not seen as many examples of submitted data as I would like.

The only rule I have is that I want the last name by itself with no comma afterwards. My issues so far have been eliminating the DVM (or potentially others like that) and getting the two part last names to properly populate ( Del Rio populates as Del).

I do not think that there will be a finite list of possibilities, as clients who are submitting this information are free to input whatever they like on the form. However, as we deal with the same types of clients over and over, I'm sure they will begin to repeat themselves. If having such a list would be helpful, could we start the list with DVM as the only possibility, and I could add to the rule each time I encounter something else I want to eliminate?

 

[Joe4]

Well, we could try wrapping them in nested SUBSTITUTE functions, but that is going to get unwieldy as the list grows.
Here is what the formula would look like to get rid of the DVM or XYZ:

=TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH(",",A2,1)-1),"DVM",""),"XYZ",""))

As the list grows, we may have to switch to another solution, possibly VBA, unless we can come up with a hard-and-fast "rule" we can use, instead of a list.
But we would need someone to define what that rule should be (if we had a list, we might possibly be able to figure out a pattern and then a rule).

 

[Flexremmington]

Well, we could try wrapping them in nested SUBSTITUTE functions, but that is going to get unwieldy as the list grows.
Here is what the formula would look like to get rid of the DVM or XYZ:

=TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH(",",A2,1)-1),"DVM",""),"XYZ",""))

As the list grows, we may have to switch to another solution, possibly VBA, unless we can come up with a hard-and-fast "rule" we can use, instead of a list.
But we would need someone to define what that rule should be (if we had a list, we might possibly be able to figure out a pattern and then a rule).

That's beautiful man, thanks for your help!

I have no idea if I'm going to have five or 500 different XYZ's, but this will work swimmingly for the time being! I might be back in a week, six months, or never, but I really apricate it!

 

[Joe4]

You are welcome.
Glad it worked for you!