The most important Google Sheet functions I use every day

Let's see why CONCATENATE, INDIRECT, and, REGEXMATCH are very powerful Google Sheets functions.

Google sheet is the most powerful, flexible, and possibly the best cloud CRM you can use. By using different add-ons and functions in Google sheet, you can achieve basically everything that you want to with your data.

I’m going to write a series of articles on Google sheets and how to expand it further with all the powerful add-ons and functions it supports. We are also going to automate a lot of work using App Scripts and other automation tools that connect with Google sheet. So let’s get started 🎉

As the title says, one of the most important functions that I use frequently, almost every day is the CONCATENATE  function in Google sheet, which is used to combine two cells together.  Let’s see all the things it can do:

What is the CONCATENATE function?

CONCATENATE function is used to join two strings together. For example, if you want to join words and make "Welcome to That Automation Guy!" you can do that using CONCATENATE like this:
CONCATENATE("Welcome"," ", "to", " ", "That Automation", " ", "Guy!")

When you can run this function in Google Sheets, you can see the output in the cell as shown below,

You can also join cells together, like =CONCATENATE(A1, A2, A3) and it will join these cells together. 

Before I show you a more advanced part with CONCATENATE, you should know how to add new lines with the CONCATENATE function.

How to add a new line with CONCATENATE function

When using the CONCATENATE function, at places you will need to add new lines and you can do that using CHAR(10) function.

Something like this:

Why we use CHAR(10) to add new lines with CONCATENATE?

A new line is also known as Line Feed. The Decimal for a Line Feed is 10 as shown in the image. You can read more about this Unicode Control Code here - https://en.wikipedia.org/wiki/List_of_Unicode_characters#Control_codes

CHAR( ) function converts a number into a character according to the current Unicode table. That’s the reason we use CHAR(10) to add news lines in Google Sheets.

We are using to use these two messages to dynamically prepare messages or emails based on keywords in other cells. Using a Simple Google Sheet, you can personalize your messages and emails for lead generation.

INDIRECT will make your life a lot easier

The INDIRECT function returns a reference to a range. The INDIRECT function does not evaluate logical tests or conditions. This function helps lock the specified cell in a formula. The benefit of this is, we can change a cell reference within a formula without changing the formula itself.

One advantage of this function is that the indirect references won’t change even when new rows or columns are inserted in our Google Sheet.

Let’s see a few examples to understand INDIRECT:

1. Suppose A1 = 43 and using the INDIRECT function, we give reference A1 as shown below to C1 using INDIRECT(“A1”)

   

2. When I write the formula INDIRECT(“D”&4) it gets converted to D4, as you can see in the attached Google Sheet, the D4 value is 90, so the value in the formula is shown as 40.

   

3. When I write the formula INDIRECT(“C” & ROW( ) ) we are referencing to the column and using the ROW( ) function, it returns the current ROW value which is 5, so the value becomes C5.

   

You can use INDIRECT in multiple ways, to read more about INDIRECT function, refer to this guide - https://infoinspired.com/google-docs/spreadsheet/google-sheets-indirect-function-examples-usage/

REGEXMATCH helps you easily match values

REGEXMATCH is used to match any plain text in word, sentence, or phrase.

Let’s see a few examples to easily understand this formula:

1. Say, a Text in Cell A1 is — That Automation Guy!

   Formula: =REGEXMATCH(A1, "Guy")
And it will return TRUE.

2. Now let’s change our formula to this — =REGEXMATCH(A1, "[a-z]")
Now it checks whether A1 cell contains any small characters from a to z. And as our text contains a few small characters, it will return TRUE.

You can use this Regex maker website to easily make complex Regex functions – https://regexr.com/

Now, it is time to combine all these three functions and make a powerful Google Sheet template which will help us dynamically generate personalized messages.

---

Personalized Email and LinkedIn Messages using a simple Google Sheet and Formulas

Ian from Hyperise helped me build this Sheet, all credits to Ian for his awesome work.

As you can see in the image above, the first section of the Google Sheet, helps you modify the text and also add condition-based text for example, if the location for someone is Mumbai the message will say “a fellow Mumbaikar” and if the headline says “Growth” then the message will be personalized to “someone responsible for growth”.

There is a total of 3 sheets, the first sheet helps you prepare your message, the send sheet contains sample data and the third one contains the final sheet of all the data you can extract from TexAu.

Watch this video to understand all the parts of this template:

Email and LinkedIn Message Template - Google Sheets — Watch Video

You can use TexAu — https://www.texau.com to extract profiles from LinkedIn or Sales Navigator Search and paste everything to this Google Sheet.

I hope you found this post interesting and useful.

In the next post, I will learn how to use Google Sheet as your central CRM to store all your contacts automatically, including from Facebook, LinkedIn, and Email.

Before that guide, I will also share Behind the scenes of what was happening last week at my apartment and how I moved all my stuff to a new apartment including all the Furniture 🪑, 🛌 in just $75 USD 😉

Make sure to subscribe to our newsletter to receive all new guides, hacks, and behind the scenes of — That Automation Guy! We are running a discount for early adopters and it is only $25 / year.

---

📣 Lastly, if you need that Google sheet template, join the “That Automation Guy!” group and comment “interested” on the pinned post. 📣

Any questions, feedback, and thoughts on this guide?

Drop your comment and let’s talk 🙏