0

I have the following 3 rows of data in cells A1:A3 in Google Sheets:

Doe, Jane
Doe, Joe
John Doe

I want the following in B1:B3:

Jane Doe
Joe Doe
John Doe

Essentially, I'm creating a new column with all names as FIRST_NAME LAST_NAME.

My approach was to use split,index,concatenate,find,isnumber,andif, which works if I apply the function individually to each single cell:

= IF( ISNUMBER( find(",",A1)), concatenate(index(split(A1,", "),0,2)," ", index(split(A1,", "),0,1)),A1))

However, I want to repeat this function for each name in Column A using a single function in B1.

I tried to simply use ArrayFormula, but this (unsurprisingly) does not have the desired result:

= Arrayformula(IF( ISNUMBER( find(",",A1)), concatenate(index(split(A1,", "),0,2)," ", index(split(A1,", "),0,1)),A1)))

Resulting in:

JaneJoeDoe DoeDoeJohn
JaneJoeDoe DoeDoeJohn
John Doe

I'm too new to Google Sheets to know if there's a looping type approach to doing this. In R, I would have this figured out in 15 seconds using an apply function.

  • Please let me know if this would be better asked at StackOverflow... – theforestecologist Sep 17 '18 at 13:48
  • You'd have to use python for this, or maybe javascript. Im not sure how a formula could differentiate first and last name (unless you want to write thousands of if else statements) – Datanovice Sep 19 '18 at 8:04
  • @Datanovice well just one if else statement I think: if a comma is present, the cell contains last then first name; otherwise the names are first followed by last. So far (I believe anyway), that's not my problem. My questions is really meant to focus on a way to essentially create a function that applies over many cells (each individually) in a loop... – theforestecologist Sep 19 '18 at 18:12
  • 1
    you could use =query() for this and apply SQL syntax over a range of cells to apply your logic. It's not the same as a for loop, but the only way to apply a for loop in google sheets if by using the app script. Although I may be wrong...! – Datanovice Sep 19 '18 at 19:29
  • 1
    It would run in one cell but return the range you're querying, if you had A2:C9 selected for example, it would return that range, if you added a where clause which was only present in two cells it would return two. Check out coding is for losers, he has really good Google query guides. – Datanovice Sep 20 '18 at 6:32
1

As we've have found, it's not difficult to write a formula to achieve this outcome. But an ARRAYFORMULA is another matter and it seems (famous last words!) that it easier said than done (assuming even that it is possible).

I've looked at a solution using a script. The code follows the same process as the formula shown in the question.

  • Establishes the last row in the column,
  • Loops through each row,
  • Gets the cell value, tests with a SPLIT, if there's a comma then we switch the names around; if not, we just use the name as-is,
  • Paste the name into the cell on the same row but in the adjacent column.

function getlast(){

  // set the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // get the last row in column A
  var Avals = ss.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;

  // start loop for each row/name
  for (var x=1; x<(Alast+1); x++) {

    // get the value of the cell
    var values = sheet.getRange(x, 1).getValue();

    // slit the cell and then measure the length
    var mysplit = values.split(",");

    // if the length = 2, then there's a comma
    // so we need to switch the names around
    if (mysplit.length == 2){

      // set variables and values
      var firstname = mysplit[1].trim();
      var lastname = mysplit[0].trim();
      var myname = firstname+" "+lastname;

      // declare an array and then assign the name
      var newname = [];
      newname.push(myname);

    }
    else
    {
      // there's no comma so no need to switch around the names
      var myname = mysplit;

      // declare an array and then assign the name
      var newname = [];
      newname.push(myname);

    }

    // paste the new name - note the offset ( same row, but over one column
    sheet.getRange(x, 1).offset(0, 1).setValues([newname]); 

  }  

}

This screenshot shows the output:

enter image description here

0

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(REGEXREPLACE({
 IF(REGEXMATCH(IFERROR(SPLIT(A1:A, " ")), ",$"), , IFERROR(SPLIT(A1:A, " "))),
 IF(REGEXMATCH(IFERROR(SPLIT(A1:A, " ")), ",$"),   IFERROR(SPLIT(A1:A, " ")), )},
 ",$", )), , 999^99))))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.