Last Name, First Name, MI

[Wulf]

There are several SIMILAR threads, but I couldn't find one that fit this:

I need to turn "Last Name, First Name MI" in cell I9 on Sheet1 into:

"Last Name" in E9, "First Name" in R9, and "MI" in AF9 on Sheet1.5, with it also showing "" (blank) in AF9 if there is no "MI" in I9 on Sheet1.

I've been able to extract the last name, and even the first name....but if that's all there is, then it puts the last letter of the first name as the middle initial, and if there is a middle initial, that cell goes blank.

????

Thanks!

 

[Scott Huish]

Better yet,

Change the AF9 formula to:

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(I9,".",""),2)))=1,TRIM(SUBSTITUTE(RIGHT(I9,2),".","")),"")

 

[Jleist]

Is there any way to create a macro for something similar to the above?

I have the following format

First Name MI Last Name in Column A.

Sometimes there is a space on the last name for someone with a last name Mc Cormick (for example)

Also, sometimes the list of names is short and sometimes the list of names is long. Is there a way to get a "universal" macro in place, that will not depend on the number of names in the list but will be able to detect when the list ends?

So far I was able to run the macro to get them from text-to-column into cells A, B, and C. However, the middle column sometimes contains the last name if there isn't a middle initial. I need a way to test the middle column for more than one character. If there is more than one character, it is a last name and needs to be in the third column.
Thank you so much for your help!