Friday, December 18, 2015

Creating a custom formula in Google Sheets to concatenate a range of values with a delimiter

In the past, I have written a little VBA to create an Excel function to do this.  I use it to take a range of cells containing email addresses and make one string containing those addresses delimited by semi-colons suitable for pasting into the TO: field of an email.

Now that I am using Google Sheets more, I was curious if I could do a similar thing.

With a little investigation, I was able to go to Tools | Script Editor and enter the following:

 function CCAT(range, delimiter) {  
  var returnString = '';  
  var rows = range.length;  
  for (r=0; r<rows;r++){  
   var cols = range[r].length;  
   for (c=0; c<cols; c++){  
    returnString += range[r][c]+delimiter;  
   }  
  }  
  return returnString;  
 }  

Once it is saved I can invoke it from my Sheet using something like:

 =CCAT(C2:C25,";")  

Notice that a range like C2:C25 is automatically converted by Sheets into a two dimensional array.

After successfully creating the function, I searched for a similar solution online and found that there are ways to do it that do not require a custom script - see
https://productforums.google.com/forum/?hl=en#!category-topic/docs/how-do-i/FQbzbVK4-i0 , however my script is illustrative if not elegant.