Automatic Search and Replace in Google Sheets with a script

1
votes

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 Jones Marta Lewis James, Jessica Jennifer Annis Bones, Raymond

In cell AF2, it says:
Jessica James Bones, Raymond Lewis, Marta Davidson, Joanna Field, 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

1
google-apps-scripttextgoogle-sheetsfind-replace
The inbuilt find/replace would require me to do it 380+ times, and if I add to it later, I'd have to do them all again. I would love to do it with formulas but I don't know how to incorporate all 380 possible changes when the text is not the entire cell. . – Elizabeth Doerfler
What's difference between a lastname and a firstname? I used to deliver newspaper to a lady named George George. – Cooper
@Eliza Explained how you would do it – TheMaster

1 Answers

1
votes

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 (\word)
  • ,: literal ,
  • \s: A space
  • +: one or more of the previous matched character
  • ?: one or zero of the previous matched character
  • (): capture group
  • $: Replacement of the numbered capture group