Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | JamesFrancisGray | Gray,JamesFrancis | ||||
Sheet3 |
Parse Names.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | JamesFrancisGray | Gray,JamesFrancis | ||||
2 | RoseSarah-JaneOlson | Olson,RoseSarah-Jane | ||||
3 | MikeB.ClarkGriswald | Griswald,MikeB.Clark | ||||
4 | JoeClark | Clark,Joe | ||||
5 | TestThisPleaseThanks | Thanks,TestThisPlease | ||||
6 | RobertWDowneyJr. | DowneyJr.,RobertW | ||||
7 | JoePaulDirtIV | DirtIV,JoePaul | ||||
8 | AlanMichaelStephanJordanIII | JordanIII,AlanMichaelStephan | ||||
9 | JohnDoeV | DoeV,John | ||||
NameParse |
=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1))))&", "&SUBSTITUTE(A1," " &RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1)))),"")