This website or its third-party tools use cookies which are necessary to its functioning and required to improve your experience. By clicking the consent button, you agree to allow the site to use, collect and/or store cookies.
The platform this site runs on doesn't have the option to turn off cookies server side, but you can do that in your browser and then return to the site.
I accept
Deny cookies Go Back

Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Nov 17 2012

How To Use Google Docs’ JOIN And SPLIT Functions

JOIN and SPLIT as demonstrated by Rihanna and Chris
Get it? Rihanna and Chris Brown split up and are together again? #clever

 

Google Docs offers two functions in its spreadsheets that Excel doesn’t: JOIN and SPLIT. They are the finessed alternatives to Excel’s CONCATENATE function and its scorched earth Text to Columns command.

JOIN Function

Let’s say you want to create a regular expression (regex) that joins together (geek speak: concatenates) a bunch of cities separated by pipe characters, which means “or” in regex. Your formula will look like this:

Here’s what it will look like:

JOIN function in Google Docs

The first argument is the delimiter and the second the array, which is just more geek speak for a range of cells. And you can join more than one array. Just separate them with commas.

Don’t ask me why Excel doesn’t offer this because it’s awesome. Before I learned about the JOIN function from this smart ginger, I would paste my list in Word and then do a search for paragraph returns (which you do by searching for “^p”) and replace them with | (which shares a key with  just above the Enter/Return key).

SPLIT Function

The SPLIT function is a formulaic approach to Excel’s Text to Columns hatchet job. The only two arguments are the cell you want to split apart and the delimiter.

So let’s say you have a list of email addresses, and you want to pull out the domains. You would do that with this formula:

SPLIT function in Google Docs

See It Here

Feel free to check out the Google Doc I took the screen captures from, if you want to kick the tires a bit. Nothing too fancy schmancy going on there.

Pro Tip

When using functions in Google Docs you have two options: You can put the value for an argument in its own cell or enter it directly into the formula. If you put it in a cell, you can just reference the cell using its address. If you enter it directly into a formula, you need to surround it with quotation marks.

You can see in both of my examples that I entered the delimiters directly into the formula, so I used quotation marks. But I could have put it into its own cell and then referenced the cell. It wouldn’t be necessary with something as simple as this, but I highly recommend using cell references for something more complicated like web scraping.

Betedubs, if you haven’t used Google Docs for web scraping, you really haven’t lived. When you start web scraping on larger websites or even Google, you’ll often find your requests can be ignored or blocked so you should learn several web scraping best practices to avoid your future web scraping from being blocked. I did a presentation at a meetup at SEER Interactive where I break this down in such simple terms, even a marketer can do it. (Oooooo burrnnnn.) No but seriously … You should check it out. I provide you with a video, Slideshare presentation, and sample Google Doc with tons of examples you can see in action. And cookies afterwards.

Learn More

You can learn more about data formatting in my Annielytics Dashboard Course, a video course that will teach you how to put your data in stilettos and work the pole. 🙂

Photo by Fanpop

Written by Annie · Categorized: Google Docs · Tagged: Google Doc Functions

Comments

  1. LOSERSPEAK says

    March 13, 2014 at 6:39 AM

    BEHENCHOD!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2023 annielytics.com