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:
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:
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. 🙂
BEHENCHOD!