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

1

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 (\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