Tuesday, August 7, 2012

User-Defined Functions Re-Visited: Use The Buit-Ins When They Are Provided

I’ve noticed that the blog entry here on user-defined functions is the most popular of the entries in this blog based on the number of page views it receives so there is clearly some interest in this topic.  This fact as well as having gained more experience in JavaScript and Google Spreadsheets has prompted me to revisit this topic.
Writing back then, I stated:

The Google spreadsheet applications has a very large number of built-in functions, check to ensure a required function does not already exist before writing a customised version.”.  

I should have heeded my own advice because one of the examples I gave could have been done using a Google Spreadsheet built-in function and JavaScript could have been avoided altogether, namely the one that concatenated a list of strings.  Google Spreadsheets come with a very neat built-in string function called JOIN which is tailor-made for this purpose.

JOIN Example
I’ve got the following entries in range A1:A5:

cat
dog
rat
mouse
cow

I want to create a single string from each of the cell entries with the individual entries surrounded by a single quote.  The JOIN function can do most of this and the task can simply be completed by prepending and appending single quote characters to the output of the JOIN function like so:

=CONCATENATE("'", JOIN("','",A1:A5), "'")

This produces the desired output:

'cat','dog','rat','mouse','cow'

The Google code on the JOIN function is brief: “Concatenates a one-dimensional array using a specified delimiter.”  The delimiter can be one or more characters but the input range must be one-dimensional, i.e. one row or one column.

Another Google Spreadsheet text function that performs the opposite task to JOIN is SPLIT which takes a string input and a delimiter to split on and returns an array of values.

SPLIT Example
Given this text in cell A1:
this:is:colon:separated:text
We would like to use the colon as a delimiter to break it up into separate cells in row 1 with the first value in cell B1
To do this, enter in the following formula into B1 and hit RETURN

=SPLIT(A1,":")

The output, ranging from cell B1 to F1 is
this is colon separated text

The SPLIT function is clearly very useful and provides a means of implementing the very useful Excel Text to Columns... functionality.  The Google documentation for SPLIT is considerably more informative than that for JOIN:
Splits text based on the given delimiter, putting each section into a separate column in the row. The string is the text you want to split. Treat_delimiters_individually is an optional True/False parameter. If omitted or True, the function will use each character within the delimiter string as a separate delimiter. If False, the function will use the entire delimiter string only to split your text.
In addition, there is a Learn More link for SPLIT that is worth reading. An important point to note on SPLIT is the possibility of defining more than one delimiter character so if our input was sloppy:
this:is:colon:separated;text
and nd used both colons and semi-colons as delimiters, we could use the following call to SPLIT to get tha same output as first time:

=SPLIT(A1,":;")

Google Spreadsheets implements most of the built-in Excel functions but also adds some of its own such as JOIN and SPLIT shown above.

A few more text functions not found in Excel that are well worth investigating are those that use regular expressions:

REGEXEXTRACT
REGEXMATCH
REGEXREPLACE

I’ll cover these functions and JavaScript regular expressions in a future blog entry.

In addition to these Google-specific text functions, there is also a very useful filter function called UNIQUE that can be used to extract, as its name indicates, a unique list.

UNIQUE Example
Given these entries in range A1:A9
cat
dog
rat
mouse
cow
cat
DOG
rat
Rat
and the formula =UNIQUEA1:A9) in cell B1 produces the following output in range B1:B7
cat
dog
rat
mouse
cow
DOG
Rat
This is much more convenient than Excel’s menu-driven UNIQUE filter.  The input and output given above demonstrate how the UNIQUE filter is case-sensitive.

Here is a link to a full list of Google Spreadsheet built-in functions.


Writing user-defined functions is both a good way to learn spreadsheet programming and to extend functionality but if the tool you are using and programming provides built-ins that will do the job, always use them. The built-ins should be bug-free (we hope) and should perform better. Also, as I have learned, Google Spreadsheets has useful functions lacking in Excel so it is worth perusing the link above carefully.

No comments:

Post a Comment