Google-sheets – How to concatenate columns for multiple rows simultaneously in Google Sheets

concatenateformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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.

Best Answer

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

Related Topic