Google Sheets at SEO specialists’ service – part III

Welcome to the final chapter of the TOP Google Sheets functions that will change your SEO work forever! You’re about to experience even more exciting transformations, as well as the Holy Grail of your quest for the eternal SEO glory … We’re going out with a bang! 

Haven’t read the first two chapters/episodes? No worries! You’ll access them here:

Google Sheets at a SEO specialist’s service – part I and Google Sheets at a SEO specialist’s service – part II. 

Ready to read about another five functions that will shake your SEO world? Here we go!

Split text to columns

Let’s start with a sorting non-function. Here’s one of the classic views you get while working on Google Search Console:

All retrieved data you’ll find in one column. Click “Data” to find “Split text to columns”. It will allow you to split the values listed in a single column. In this case, the values are separated with commas, but it’s up to you what the separator will be. Enjoy your freedom of choice

What do we need it in SEO for?

• splitting our meta tags into smaller elements, e.g. when we want to use the <title> structure to build meta descriptions;

• separating URL addresses, e.g. when we want to separate the type of protocol from the entire address or separate subdirectories;

• reports exported in csv format.

Tip: Remember! After splitting, the data in the splitting cell will be overwritten. So if you plan to keep it, create a copy of it before using the function

QUERY function

=QUERY(data, query, [headers])

QUERY definitely should become part of your SEO life It’s illustrated best by the true-life case below. Let’s say you have an extensive report on the crawler for the entire website that, as you know, checks loads of parameters. The columns with data/values are horizonless and the only thing you can think of is optimizing your meta titles and descriptions on the blog. So, open a new tab and use QUERY to request the import of the relevant columns, i.e. a URL  address (inc. the blog address), a meta title, or a meta description. Additionally, you can configure them any way you want.

All this happens without removing or hiding redundant columns! Oh, and one more thing! If you change the source data, the data generated with QUERY will also be updated and that’s why we love this function As in our example, just paste in a new report to quickly verify that all changes have been correctly implemented on the page.This is only a fraction of the range of features that this function offers. We can immediately import sums for the indicated group, group by additional parameters, and even format the imported data. Interested? I encourage you to test all the language clauses from the Google table.

What do we need it in SEO for?

• to share only selected data from our worksheet e.g. when creating reports for the client,

• to analyze extensive crawls or other types of reports;

• to maintain (with low effort) current data throughout the client’s document;

• to connect, organize and filter large databases.

Tip: If you want to import data from a separate sheet, use the IMPORTRANGE function. All you have to do is enter the URL of the source sheet and the tab from which you want to import data. Then, the magic happens

=IMPORTRANGE(spreadsheet_url, range_string)

Tip2: What if …………. Good thinking, Holmes, You can import data from a separate sheet while filtering it using the QUERY function. You must remember though to change the letters of the columns into the numbers (column A to Col1, B to Col2 etc.) One hell of a mix!

Here’s a tiny tip — make sure you first fire up the very IMPORTRANGE function to approve the connection between the files.

UNIQUE function

=UNIQUE(range, [by_column], [exactly_once])

Unique — that’s a key feature of this function. It will allow you to ’filter’ a list and sieve off duplicate values. What is more, it does not overwrite the output data. Let’s clean the list of URLs below of duplicate addresses. Yes, colors have been added to reinforce the message.

What do we need it in SEO for?

• to remove duplicate keywords from our list;

• to organize our website’s URL list, keeping only unique addresses;

• to clean any databases of duplicate information.

FIND function

=FIND(search_for, text_to_search, [starting_at])

If someone ever does a fair ranking of the most popular keyboard shortcuts, I’m betting that Ctrl+F (search) will be on the podium!It will only give way to the classics of the genre, Ctrl+C, Ctrl+V FIND is just like Ctrl+F, but in the form of a FUNCTION. Thanks to this, we can not only search the indicated cells to find a phrase, but above all combine it with other functions. You don’t feel it yet? Let’s look at a simple example. We want to quickly divide our keywords into brand and non-brand keywords.

What do we need it in SEO for?

• to check whether a key phrase is included in the title or H1 header (we can point to a specific cell in the “search_for”);

• to quickly map product pages;

• to categorize keywords;

• to categorize pages based on the URL structure;

• internal linking.

REGEX functions

3 sister formulas that have one thing in common – the use of REGEX (regular expressions). Don’t you worry! Once I’ve introduced our ‘sisters’, I’ll leave you with a mini glossary that will make it easier to use the functions below. REGEXes will also come in handy in other tools, such as Google Search Console, Ahrefs, or Screaming Frog.

=REGEXEXTRACT(text, regular_expression)

For starters, REGEXEXTRACT extracts the fragment we need from a string or cell. We have already seen functions that offered similar features, but the use of regular expressions allows you to be more accurate and concurrently search for many parameters. Let’s find the addresses that use the HTTP or HTTPS protocol:

=REGEXMATCH(text, regular_expression)

REGEXMATCH has the same structure as the previous one. The difference is in the response it returns. This function returns TRUE or FALSE depending on the conditions expressed by the regex. It blends with the IF function better than peanut better and black currant jam. Let’s have another look at our example with brand phrases. This time, let’s take into account that the user could also search for the abbreviated name of our brand „ctsw”

=REGEXREPLACE(text, regular_expression, replacement)

REGEXREPLACE takes us a step further and replaces the found value. We’re going to migrate our salestube website onto salestube.com domain:

What do we need it in SEO for?

• to extract a specific item from the list of URL addresses, e.g. http status, domain name or language directory;

• to update meta and headers, e.g. by updating the year in the title;

• to create new URLs while working on site migration;

• to categorize pages and keywords;

• to extract more specific data from any database.

And now the cheat sheet I promised  

Use (or best, combine) the special characters listed below to select the text:

• ^jacket – starts with a “jacket” string;

• jacket$ – ends with a “jacket” string;

• jacket|pants — contains the phrase “jacket” OR “pants”.

Use these special characters as well:

• . — replaces any one character;

• — an asterisk indicates that the preceding character occurs 0 or more times’; we combine it with a dot (“.”); it shows that the indicated phrase may be followed (or preceded) by (any number of random characters);

• + — similar to the asterisk case, but the preceding character must occur at least once;

• https? — it will find us http or https’ “?”; it makes the preceding character optional, occurs once, or does not occur at all

• [] — range of characters, we can indicate the group of characters that we want to use (or their range);

• {} — indicates a specific number of characters that should occur, e.g. “^.{5}$” will show us all the strings that contain exactly 5 random characters. We can also use them to set the minimum and maximum characters. Then, we need to we define 2 values: “^.{2,5}$” and we get all the strings consisting of 2 to 5 characters;

• \ — an escape character… if we want to use any of the indicated special characters (e.g. a dot as a regular dot in a URL), we must precede it with a \ to disable its “magic” properties. For example, we will enter “.*\.pdf$” to find all pdf files (addresses ending in “.pdf”) on the list of URLs.

It’s only a sneak peek of the potential that regexes offer. Actually, we easily could produce a separate tutorial on them. As there are loads of combinations, they guarantee plenty of twists and turns. One more tip before we go! Use https://regex101.com/ to test the regular expressions for accuracy.

Wrap-up

You know what they say: all good things must come to an end, and so it is with the Google Sheets at SEO specialists’ service trilogy. However, looking at the vast range of the features Google Sheets offer, I dare say that there will always be something left to discover … Use these few functions and tricks as a basic set of Lego bricks. You can build anything from them, if only you reach beyond the standard ’user manual’ I know you’ll figure the stuff out!