How about this nightmare?

[MrBurn5]

I've been handed a data set with 70,000 entries - that were put together by someone lacking vision and understanding. They put in Column A the employees' entire name - in varying forms - and we need to extract it all. in to new cells for first, middle initial/name, last, suffix.


Below is an example of things we've seen:

Excel 2016 (Mac) 32 bit

	D
8	Jeff Smith
9	Joe G. Smith
10	Jerry G. Smith Jr.
11	Scott Smith Jr.
12	Joe Gerald Smith
13	Thomas F. Gerber
14	Thomas Jackson
15	George M. St. Clair Jr.
16

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I know it's not a lot to go off of, but if anyone has any suggestions on how to start, it would be appreciated.

 

[alansidman]

Look at this link for a VBA solution

https://www.experts-exchange.com/qu...-Middle-Last-and-Suffix-into-four-fields.html

 

[inactiveuserps07]

@MrBurn5, here's a formula solution, if you'd prefer that to VBA.

Create four columns to the right of the original name column for First, Middle, Last, Suffix. Since your post showed the original names in Column D, I'll assume the aforementioned columns will be in E, F, G and H respectively.

Assuming also that your original names start in cell, enter these formulas into E2, F2, G2 and H2; then select those four formulas and copy-drag them down as far as you need them:

E2:

=LEFT(D2,FIND(" ",D2)-1)

F2:

=IF(LEN(D2)-LEN(SUBSTITUTE(D2," ",""))>1,IF(RIGHT(D2,1)=".","",LEFT(SUBSTITUTE(D2,E2&" ",""),FIND(" ",SUBSTITUTE(D2,E2&" ",""))-1)),"")

G2:

=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(D2,E2&" "&F2,"")),H2,""))

H2:

=IF(OR(RIGHT(D2,1)=".",ISNUMBER(FIND(" I ",D2&" ")),ISNUMBER(FIND(" II",D2)),ISNUMBER(FIND(" III",D2)),ISNUMBER(FIND(" IV",D2))),MID(D2,FIND("zzz",SUBSTITUTE(D2," ","zzz",LEN(D2)-LEN(SUBSTITUTE(D2," ","")))),10),"")

You'll need all four formulas in place for the system to work, since they are interdependent.

This solution will cover all scenarios shown in your original post above, as well as a few others (e.g., suffixes such as I, II, III, etc.).