Phone numbers are frequently used data types. So you would think they would be straightforward to work with, right? Right?

Well if you have phone numbers from multiple people and countries, it gets to be a mess.

You may find yourself having a spreadsheet with a bunch of different phone numbers and you’re stuck figuring out how to clean them.

If you want a quick way to do it, check out Clean Spreadsheets!

However, if you are trying to code or manually clean them yourself here is what you need to keep in mind.

What would cleaning a phone number do?

Cleaning a phone number will allow you to determine whether the number is valid and separate it into its components such as country code, area code and subscriber number. …


Automating sending emails from a Google Sheet filled with names, emails, messages etc. is a common task for any person or business.

Image for post
Image for post
Send Email

This tutorial is opinionated, i.e. it outlines one specific way we found best to automate sending emails. We also use our own product API Spreadsheets to get the data from your Google Sheet.

There are many ways to go about this all and you should modify the code to fit your needs.

Pre-requisite

If you do not know how to use the Terminal and Python, then go through this tutorial first: Intro to Reading and Writing Spreadsheets with Python

Google Sheet

We are going to use a sample Google Sheet for this tutorial. …


People or customer names are frequently used data types. So you would think they would be straightforward to work with, right? Right?

You may find yourself having a spreadsheet with a bunch of different customer names and you’re stuck figuring out how to clean them.

If you want a quick way to do it, check out Clean Spreadsheets!

However, if you are trying to code or manually clean them yourself here is a general overview on what you need to keep in mind.

First… what is a Name?

Image for post
Image for post

This may seem like a question that doesn’t need to be answered. Everyone and everything has a name. Animals, places, people, things are all defined and known by a word or combination of words that becomes their identity. …


Marketing is a creative enterprise. But it is also a data-driven enterprise. And any data driven enterprise needs clean data to work effectively.

Image for post
Image for post
Photo by Franki Chamaki on Unsplash

Data analysts and professionals recognize the importance of cleaning your data. But it might not be obvious to Marketing professionals.

Here are 5 reasons why your marketing effort needs clean data

1. Duplication

Customer and user duplication might be the single most important reason to strive for clean data.


Date and time are frequently used data types. So you would think they would be straightforward to work with, right? Right?

The ISO (International Organization for Standards) even has a module dedicated to how date and time should be written.

Image for post
Image for post

But like all data, date and time frequently appear in the wild in all different kinds of formats.

In this guide, we will walk through the most common date and time formats. There will also be detailed steps on how to best handle them in your Data Analytics project.

SHAMELESS PLUG: If you need to convert any date/time, feel free to check our tool www.cleanspreadsheets.com that lets you automatically clean any date format in spreadsheets.


First of all, what is geocoding?

Good question! Well, geocoding is the process of turning an address into a coordinate that specifies its latitude and longitude values. This is called Forward Geocoding.

There is also reverse geocoding. Which allows you to convert latitude and longitude values into an address.

Geocoding allows us to easily locate addresses on a map and is often used for mapping, geographic analysis, spatial analysis, etc.

Image for post
Image for post
A typical forward geocoding process

Hmm, ok… So what’s this article about?

I have analyzed and prepared a list of of several no-code tools and applications that don’t require any technical knowledge to Geocode.

All you need is a list or spreadsheet of addresses.

These tools accept lists of addresses, often in spreadsheets or csv files, and either do Forward Geocoding or Reverse Geocoding, or often both. …


What does a SQL Join mean?

A SQL join describes the process of merging rows in two different tables or files together.

Image for post
Image for post

Rows of data are combined based on values in a selected column.

In the example above, this is the Item column.

An Item, Apple, has both a price and a quantity, but in different tables.

Using a SQL join, these values become merged into one row!

So why do I need an entire article about this?

In most cases, joining data won’t be as simple as the previous example. Oftentimes, we’ll have rows that can’t be joined because there isn’t the same value in the joining column for both tables.

For instance, what if there wasn’t a quantity for apple in the example above? How we handle what we do with rows like this depends on the type of SQL Join we choose. …


DISCLAIMER: This article uses our product API Spreadsheets, which our users say is the easiest way to save web/html form data to Google Sheets. It has a generous free tier.

If you are like us and often create websites where nothing needs a server and a database except a few forms, then you must also get frustrated with all the extra work you have to do to simply save data from a few fields.

Step 1: Set up a contact form in an HTML file or through Google Form

Okay, if you don’t know any HTML, then this might be a steep learning curve. The example is small so you should Google terms you don’t understand and it might make sense.


What do you even mean by merging spreadsheets?

The definition of merging spreadsheets is right in its name — we want to combine two or more spreadsheets into a single spreadsheet. This is a really essential skill to learn for any novice Excel users out there.

Interesting… what should I already know?

If you know how to make simple tables and organize data in Excel, you’re ready to go! As a general rule of thumb, the first row of data is usually composed of headers and each file (a workbook) can be composed of several pages (spreadsheets).

So, what exactly is in this article?

I’m going to start with the easiest types of spreadsheet merging and then move onto the harder types of merging. After that, I’ll include some common problems I ran into and some other methods of merging spreadsheets that you could use! …


DISCLAIMER: This is about our tool API Spreadsheets. We have been told it’s an easy and obvious way to share spreadsheet data amongst a team.

How do you currently share your spreadsheet data with other data peoples? Let me guess, you download your Google Sheet or Dropbox file. Or wait a minute, was the file in your email sent 3 days ago? Oh crap, it was on your work computer wasn’t it. Okay you found the file.

Now you email it to John to run some analysis. He runs his analysis. He sends it back to you. You re-run your old models…..and What the Hell?! The numbers don’t make sense any more and oh my god, your boss will be mad, you spent so much time doing this. …

About

Love Spreadsheets

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store