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’ve got the following entries in range A1:A5:
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:
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.
Given this text in cell A1:
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
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:
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:
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:
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.
Given these entries in range A1:A9
and the formula =UNIQUEA1:A9) in cell B1 produces the following output in range B1:B7
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.