Using JOIN and REGEXEXTRACT with ARRAYFORMULA to Switch First and Last Names Not Working-CodePudding

I have a column of names in my spreadsheet that are structured like this...

Albarran Basten, Thalia Aylin

I'm using the formula below to extract every word BEFORE the comma (last name), and then only the first word AFTER the comma (first name), and then switch their places. It works great.

=join(" ",REGEXEXTRACT(D2,",\s(\S )"),REGEXEXTRACT(D2,"^(.*?),"))

The formula above returns the name mentioned above like this, exactly as I need it to...

Thalia Albarran Basten

But, when I try to get it to automatically update the entire column of names using ARRAYFORMULA, it joins together all the names in the column all together into one cell, in each of the cells all the way down the column. Here's the formula I'm using that won't work...

={"Student Full Name";arrayformula(if(D2:D="",,join(" ",REGEXEXTRACT(D2:D,",\s(\S )"),REGEXEXTRACT(D2:D,"^(.*?),"))))}

Any idea on what I could change in this arrayformula to make it work? Thanks for your help.

CodePudding user response：

You can replace your REGEXEXTRACTs with a single REGEXREPLACE:

REGEXREPLACE(D2:D, "^(.*?),\s*(\S ).*", "$2 $1")

Or,

REGEXREPLACE(D2:D, "^([^,]*),\s*(\S ).*", "$2 $1")

See the regex demo.

Details:

^ - start of string
(.*?) - Group 1 ($1): zero or more chars other than line break chars as few as possible
, - a comma
\s* - zero or more whitespaces
(\S ) - Group 2 ($2): one or more non-whitespaces
.* - zero or more chars other than line break chars as many as possible.

CodePudding user response：

With your shown samples please try following regex with REGEXREPLACE.

REGEXREPLACE(D2:D, "^([^,]*),\s*([^\s] )\s\S*$", "$2 $1")

Here is the Online demo for used regex.

Explanation: Adding detailed explanation for used regex.

^         ##Matching from starting of the value.
([^,]*)   ##Creating 1st capturing group which matches everything before comma comes.
,\s*      ##Matching comma followed by 0 or more occurrences of spaces here.
([^\s] )  ##Creating 2nd capturing group where matching all non-spaces here.
\s\S*$    ##Matching space followed by 0 or more non-spaces till end of the value.