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.
>Solution :
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.