I have two reports that have employee names in different formats.
File 1 is last comma first; file 2 is first space last.
Some have middle initials; some have two first names; some have two last names.

I have a macro set up to use a helper column and make each file first pipe last.
When I wrote the inital macro, I did not take into account two last names (initial data did not have any).
Now I'm stumped on how to extract the second last name and first name in the comma file.

Currently:
File 1 has DELA CRUZ, DAVID. Macro creates CRUZ,|DELA
File 2 has DAVID DELA CRUZ. Macro creates DAVID|CRUZ

File 2 is fine; I need to get file 1 to do the same.

Current formula for file 1 is
=IF(IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)=2,MID(SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))),FIND("^",SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))))+1,256)&"|"&LEFT(C2,FIND(" ",C2)-1),IF(IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)=3,MID(MID(MID(SUBSTITUTE(C2," ","^",1),1,256),FIND("^",SUBSTITUTE(C2," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(C2," ","^",1),1,256),FIND("^",SUBSTITUTE(C2," ","^",1)),256))-2)&"|"&LEFT(C2,FIND(" ",C2)-1),""))

Basically, if C2 has two words, give me the last word pipe then first word. If C2 has three words, give me the second word pipe then first word.
I think I need it to evaluate where the comma is.
If there are three words and two are after comma, give me second word pipe then first word.
If there are three words and two are before comma, give me last word pipe then second word.
I'm just not sure how to add that comma stipulation in. This one is already fairly complicated for me.

After this step completes, the macro adds another helper column and removes the comma, so that does not need to be included in this step.

Thanks in advance!