Google Sheets tutorial: How to add cells with data using Google Sheets formulas and functions

In certain cases, cloudHQ parser will not be able to extract data from your email body, email subject, or to parse BCC/CC fields. But you can use very powerful Google Sheets formulas to enrich your Google spreadsheet.

Here are some examples.

How to parse cells with have delimiters (like CC, BCC, etc.)

Some Google Sheets cells might have multiple values separated by commas. You can extract them individually so each value would occupy its own cell as opposed to be all grouped and separated by commas.

For example, if your Google Sheets BCC cell has the following content:

email1@gmail.com,  email2@gmail.com, email3@gmail.com, email4@gmail.com

And the last columns which will look like:

=SPLIT(A1, ",")

(replace A1 is a cell with BCC). That will create additional columns at the end.

An example Google Sheet is here

chrome extension

How to parse message body or cell valuse

Lets, suppose that you parsing email messages have a similar data structure in each line of text BUT the different amount in each of the lines.

Example of mail text (content)

CODE: 091b05  COUNT: 0  EXPECTED: 1  LOCATION: 6D U1 DB-ID: 58
CODE: 091af3  COUNT: 0  EXPECTED: 1  LOCATION: 10D U1 DB-ID: 84
CODE: 091af2  COUNT: 0  EXPECTED: 1  LOCATION: 13G U1 DB-ID: 107
CODE: 091a1f  COUNT: 0  EXPECTED: 1  LOCATION: 15C U1 DB-ID: 116

Now, lets support you need to extract the text after LOCATION and before DB into for each row and add that to the Google Sheets.

To solve the above problem you can use Google Sheets REGEXREPLACE file in combination with SPLIT function.

  1. Add column which parses the above text and extract only text after LOCATION and before DB.

    =TRIM(REGEXREPLACE(A1,"CODE.*LOCATION: (.*)DB.*", "$1,"))
  2. Add last column with with SPLIT function which will transform column created in the pervious step into multiple cells: 
    = SPLIT(A2,",")

An example Google Sheet is here

chrome extension