Format Names “Last Name, First Name” in Google Sheets

I was tasked recently with taking the full names of clients (which could by anything from just a single word to a sentence (although I only factored in names three words long) and returning only the last and front name (in that order) in all caps separated by a comma.

This was something I had never done before but after a lot of testing this is what I came up with.

Function:

This function is simple enough in that it takes a name from cell “A2” and turns it into a formatted surname and forename:

=IF(LEN(TRIM($A2)) - LEN(SUBSTITUTE(TRIM($A2), " ", "")) = 1, 
  ARRAYFORMULA(TRIM(REGEXREPLACE(UPPER($A2), "(.*) (.*)", "$2, $1"))),
  ARRAYFORMULA(TRIM(REGEXREPLACE(REGEXEXTRACT(UPPER($A2), "(.*)"), "(.*) (.*) (.*)", "$3, $1")))
)

Note: This code assumes the full name is in a single cell.

Example:

Before:

John Jonny Jhonson

After:

JHONSON, JOHN

Hopefully this helped someone other then myself (and then that person leaves a comment or a like hint-hint).