The idea that stories may be found in data, as opposed to interviews, tips, or traditional journalistic sources powers a growing portion of content online today. This is great for content inspiration and scalability of content types. But can lead to a number of inefficiencies for content creators yet to develop skillsets for dealing with data. Along with our guide recently published on techniques for scraping information, below are some of the simplest and most useful Google sheet functions for enhancing your workflow.
Basic Google Sheets Syntax and Concepts
First of all, we’ll be using the term “string” a good deal in this article. For the purposes of this article, a string is a type of data comprised of a set of characters including letters, numbers, spaces, and other symbols. A string is most often delineated as some text inside of quotation marks. A string may be understood as distinct from a boolean value (True or False), numbers, dates, or error codes. In the case of Google sheets, these data types of distinct through the fact that they may be operated on in different ways. For example, a number (technically a ‘decimal’ type but for simplicity’s sake a number) may be multiplied in Google sheets. If cell A6 holds the number 2, one may type =A2*2 into B2 and the value that will return will be 4 (example 1.1). If cell A7 holds the word “test” and one tries to multiply this value, an error will be shown. This helps to show that they are different “types” of data that may be operating on differently.
In this article we won’t be touching on data types other than strings much, though you are likely to have seen other data types if you’ve spent much time in Google sheets or similar programs. Other data types include boolean values, dates, decimals (also used by whole integers), double precision floating point numbers (numbers saved to two decimal points), times, and error messages. All of these data types may be operated on in slightly different ways and particular functions will specify what data type can be passed into a function at what point.
Google sheets and similar programs allow one to perform simple arithmetic and logical evaluations that are often part of functions. Some basic syntax related to these processes include the following:
- = when placed as the first character in an empty cell the equal sign let’s Google sheets know that a function or arithmetic operation follows.
- + used for adding two entities. Short for the =ADD() function.
- * used for multiplication of two entities. Short for the =MULTIPLY() function.
- / used for division. Short for the =DIVIDE() function.
- , commas are used for denoting different parameters that may be passed to a function. For example, the =ADD() function may take two or more parameters that are numbers, they are separated by commas thus =ADD(3,5,8). The result of the preceding formula is 16.
- ( ) parentheses are used to denote the start and end of a function. They are also used to organize order of operations in the order that is typically performed (P.E.M.D.A.S).
- ” ” ‘ ‘ single or double quotation marks are used to surround a string. Both may be used, though a double quotation sign is only closed with another double, or a single quotation mark by another single. This is a useful distinction as seen in the following descriptions.
- AND the logical operator “and.” Used for checking whether both of two values are true or false. Example: (A2>1) AND (B2 >1) would return a value of TRUE if both component statements were true, FALSE otherwise.
- OR the logical operator “or.” Used for checking whether one of a series of statements is true. Returns TRUE if one of a group of statements is true, otherwise returns FALSE.
Removing X Characters from the Left or Right of a String of Text
Now that we’ve covered some basic syntax, we move to tackling our first problem. Let’s say you have a series of values as in example 2.1 and you would like to separate the college names from the preceding number values. While you could alter every entry by hand, this becomes particularly time consuming if you have a number of entries of any size. Below are two simple ways to separate the component parts of column A.
If you want to keep both values
The =SPLIT() function enables you to choose a value in a cell (or series of cells) at which to split the contents of the cell into two cells. For exact documentation of what the =SPLIT() function can achieve, see Google Sheet’s documentation of the SPLIT function. If you visit the documentation you’ll notice the following description and examples of what parameters may be passed to the function.
Notice the syntax provides for three parameters of which the third is option. The first parameter to be passed to the split function is the text to be split. The second is the delimiter, or the text around which to split. And the third is not germane to our discussion here, though is used for specifying whether or not you would like to split words into parts (the default value for this third parameter is TRUE, meaning to disable it one would include FALSE as the third parameter, or one would just not specify a third parameter).
The third sample usage of SPLIT above is the most clear and one of the most common uses. Note a value for a cell is passed as the first parameter, and a string with a value of a comma is passed as the second parameter. This means that the result of the split function will be every chunk of text between commas from A1 spit out into their own cells. An example may be seen below.
Note the difference between the commas used to seperate parameters, and the comma that is part of a parameter. Because our second parameter (“,”) is encased in quotation marks it is not treated as a literal comma, but rather a string (similar to “A” or “Cat” or “Hello”). One way we can obtain the difference