I'm trying to put the last name and "," ahead of the first name and succeeding names. I've managed to get it up to 3 names but now I have to account if the person has 4 names in their whole name. Mainly using this for Google Sheets.

Here's what I use for the 3 names.

=MID(TRIM(A2)&", "&TRIM(A2),IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=1,FIND(" ",TRIM(A2)) 1,FIND(" ",TRIM(A2),FIND(" ",TRIM(A2)) 1) 1),LEN(TRIM(A2)) 1)

so that turns "John Doe Smith" into "Smith, John Doe"

Anyone can help to account for 4 names?

CodePudding user response：

Use regexreplace(), like this:

=regexreplace(A2, "(.*) (\w )", "$2, $1")

To learn the exact 

CodePudding user response：

Use

=REGEXREPLACE(A2,"(\w  \w ) (\w )","$2, $1")