Compile a report by channel

Almost nobody is as good at Excel as they pretend to be in job interviews. One thing I see is people manually finding/replacing row by row to compile simple channel reports.

Learn

Text Extraction

If you master LEFT, MID, and RIGHT, you can extract any text from any cell and pivot to compile whatever reports you need automatically, without any manual find/replacing...More

Experience

Mike Taylor

Built a 50-person growth agency.
Logo
Logo
Logo
💪 Useful 0
😓 Difficult 0
🎉 Fun 0
😴 Boring 0
🚨 Errors 0
😕 Confusing 0
🤓 Interesting 0
Premium subscription required.
Excel experience recommended.
1. Scenario
Upsert Offices – End of the Quarter
You’re preparing for the end of quarter reporting, and part of your job is to provide a list of the leads by channel. Unfortunately the output you got from the development team is all joined together.
Sally Valentine
at Upsert

One thing you can handle for me

Please take this report from the dev team and untangle it

Right now the source, medium, and campaign are all joined by pipes

But we need a pivot table just showing leads by medium and source

Thanks!

This course is a work of fiction. Unless otherwise indicated, all the names, characters, businesses, data, places, events and incidents in this course are either the product of the author's imagination or used in a fictitious manner. Any resemblance to actual persons, living or dead, or actual events is purely coincidental.

2. Brief

In Excel and Google Sheets the LEFT, MID, and RIGHT functions are useful for a wide range of operations where you need to extract text from a cell. The LEFT function is useful for grabbing all the text to the left of a specified number of characters in. The MID function extracts the text starting at a specified number of characters in, of a specified length. The RIGHT function starts from the right and extracts everything that’s a specified number of characters to the left of that. There is also the LEN function, which tells you the length of the string in a cell. Combine these with the FIND function, which lets you identify how many characters into a text a specific string or character is, and you can flexibly and automatically extract almost any text from a cell. For example if your marketing source is delimited by pipes (|), you can combine these formulas to extract out the source, medium, and campaign into separate cells. These formulas are more accessible and understandable than using REGEX, but more flexible and useful than Text to Columns, so they should have a place in any data-driven marketers’ toolbox.

3. Tutorial

00:01 Hey, okay, let's learn how to do a report by channel. And this is a good example of the type of internal messy data that you might get from a development team.

Upsert Leads
Download
4. Exercises
5. Certificate

Share This Course