I do not typically work with scripts, but I am fairly confident with formulas:
I have a list of student names in a set of cells.For example:
In cell AF1, it says:Tommy JonesMarta LewisJames, JessicaJennifer AnnisBones, Raymond
In cell AF2, it says:
Jessica JamesBones, RaymondLewis, MartaDavidson, JoannaField, Jennifer
And so forth for more than 150 cells in that column.
All of the affected cells are in column AF (they are created from a concatenated list - so they are the result of a different formula).
I need to make sure that they are all Last name, First name.
I have been trying to copy/paste from other people's scripts that seemed useful, but I've gotten frustrated and deleted all of them:
I have tried to create a long script that included:
var to_replace = "Jonathan Adkins";var replace_with = "Adkins, Jonathan";
var to_replace = "Joshua Adkins";var replace_with = "Adkins, Joshua";(all 400 names)
and one that included:
replaceinsheet(sheet,'Jonathan Adkins','Adkins, Jonathan');replaceinsheet(sheet,'Joshua Adkins','Adkins, Joshua');(all 400 names)
and one that listed them this way:replaceInSheet(values, 'Jonathan Adkins', 'Adkins, Jonathan');
and I've tried to create additional columns beside my concatenated column that has the names and using this formula:creating a script to define =arrayreplace and then using this formula.=arrayreplace(AF1,AI2:AI,AJ2:AJ)
I'm stuck. Can someone please help?
I've created a sample sheet to hopefully show what I'm talking about:
https://docs.google.com/spreadsheets/d/1gyAdIxletLuJzubVkOmgYmlgt5iw0Ezul1kS4uLbBu8/edit?usp=sharing
Any help would be GREATLY appreciated
Best Answer
Inbuilt Find and Replace:
- Select the range you want to change: Sheet1!A1:L10
- Edit >Find and replace
Find:
(\w+),?\s+([A-z]+)
Replace:
$2, $1
Checkmark: Use regular expressions
- Click Replace All
Formula:
Alternatively, You could use,
=ARRAYFORMULA(REGEXREPLACE(L2:L10,"(\w+),?\s+(\w+)","$2, $1"))
Explanations:
\w
: A letter (\w
ord),
: literal,
\s
: As
pace+
: one or more of the previous matched character?
: one or zero of the previous matched character()
: capture group$
: Replacement of the numbered capture group