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.

Using IronSpread and Regular Expressions in Excel

I get a lot of traffic on this blog related to Regular Expressions in Excel, much of it from http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function.  There are comments there that indicate that it does not work with Office 2010.

I stumbled upon www.ironspread.com, which allows you to use Python to "program" Excel 2007 and 2010 sheets.  Python has excellent support for Regular Expressions.  It looks like there are other libraries for Python that can be used with Excel and OpenOffice; PyUNO for one.

I quickly cooked up the following example using a regular expression for valid email addresses found at http://www.regular-expressions.info/email.html.

The Excel sheet looks like:

The Python program looks like:

 # see http://www.regular-expressions.info/email.html for discussion about  
 # regular expressions to check email addresses  
 import re  
 emailRE = r"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b"  
 for i in range(1, 100):  
   currCellValue = Cell(i,1).value  
   if currCellValue == 0:  
     break  
   try:  
     m = re.match(emailRE, currCellValue).group(0)  
     if m == currCellValue:  
       # matches full address  
       Cell(i,2).value = ""  
       Cell(i,3).value = ""  
     else:  
       # matches part of address  
       Cell(i,2).value = "Suggestion"  
       Cell(i,3).value = m  
   except:  
     # no match  
     Cell(i,2).value = "Invalid"  
     Cell(i,3).value = ""  

(formatted with http://codeformatter.blogspot.ca/)

and once finished, columns B and C are filled in

I am a novice programmer in this environment, so I hope there are no glaring newbie mistakes and that the example is illustrative.

IronSpread installs a Python editor, IDLE, and a command shell which can be used to test your work.  I still love Sublime Text 2 as an editor, so give a command like:
execfile("c:/Users/Ross/Documents/EmailAddresses.py")
in the shell to run the program saved by Sublime.  Any print statements will output to the shell.  I have linked to the Excel Sheet and Python program above if you want to try them out.

Regular Expressions in Excel

Once I learned a bit about regular expressions in Actionscript 2, I wanted to use them in Microsoft Excel.

It turns out that there is a VBA extension for Regular Expressions and a installable set of custom functions for Excel that includes regular expressions. To make it work, here is what I did:

1. In Excel, hit alt-F11 to bring up the code screen.
2. Choose Tools | References and check Microsoft VBScript Regular Expressions 5.5
3. Close Excel.
4. Download and install Morefunc 5 from download.cnet.com.
5. Open Excel and start writing functions like "=REGEX.SUBSTITUTE(V2, " .......$| ....... |^....... |^......$|^.......$"," ???????? ")"

To learn more, check out the Morefunc documentation, sites like this, or sites that I referred to in my previous post. You can also write your own functions like lispy does.

Open Question: What do Excel for Mac users do?

Pretty soon you will be ordering this cup: