12 Google Sheets Formulas for SEO Keyword Research & Content Analysis

Whenever you are doing keyword research or analysing content, you will likely work with a large dataset of keywords or URLs, and accompanying data points. In this context, categorising and cataloguing this data is a great way to better understand your dataset, quicker.

In this guide (which I keep updating with new, relevant formulas), I’ll show you different Google Sheets formulas to help you organise and understand your keyword universe, or your content performance analysis datasets. Here’s what I’ll cover:

• Search intent classification with keyword-based rules: informational, commercial, transactional, localised and navigational intent, plus a single formula that returns all of them.
• Opportunity mapping and scope: SERP features as intent signals, branded versus non-branded keywords, the content types Google is featuring, and pulling question keywords into their own list.
• Content analysis: site sections and languages from URLs, ranking opportunity buckets, and brand names from URLs.
• Levelling up: filling a whole column from one cell with BYROW, MAP and LAMBDA, saving formulas as named functions, and where Google Sheets’ new AI() function fits.

A quick note on how to read the formulas: like in the original version of this guide, C2 is the cell with your keyword, J2 is the SERP features column from your keyword research tool’s export, and A2 is a URL (or, for the ranking buckets, a position). Paste the formula into row 2 of an empty column and drag it down, or use the whole-column versions at the end.

Google Sheets formulas to help with SEO keyword research: search intent classification

In my article on custom dimension formulas in Data Studio, I’ve shared a pretty robust version of a search intent classification formula. In the following few subsections, I will show you what that might look like as Google Sheets formulas. This was the basic formula template I shared in 2024:

=IFNA(IFS(REGEXMATCH(C2, "{enter keywords}"), "Informational", REGEXMATCH(C2, "{enter keywords}"), "Transactional", REGEXMATCH(C2, "{enter keywords}"), "Commercial"), "Unclassified")

IFS checks each condition in order and returns the label of the first one that matches. If nothing matches, it returns #N/A, which is why it’s wrapped in IFNA(..., "Unclassified").

If you want to add more intent buckets, you simply add a new layer of regex to the formula. The 2024 version of this extended template had a couple of misplaced brackets (so Sheets would refuse it), so here it is, fixed and filled in with the keyword lists from the sections below. The (?i) makes the match case-insensitive and the \b on each side means whole words only:

=IFNA(IFS(
  REGEXMATCH(C2, "(?i)\b(how|why|does|which|when|who|guide|tutorial|learn|examples|resource|ideas|tips|difference between|strategies|ways|example|directory|news|basics|concepts|troubleshoot|resolve|risks|beginners|upgrade|improve|optimize|case study|best practices|templates|pros and cons|analysis|articles|audit|benefits|blogs|build|calculate|campaign|choose|common questions|consider|cost|create|definition|definitive guide|earn|easy steps|essential|events|explainer video|factors|find|fix|grow|hacks|how to|ideal|importance of|increase|infographic|information|meaning|measure|metrics|mistakes|myths|opportunities|popular|practices|questions|remove|set up|shortcuts|stats|strategy|study|successful|tactics|techniques|terms|things to know|time|trends|tricks|types|updates|well explained|tutorials|framework|checklist|process|plan|test|glossary|sample|survey|exercises|report|certification|webinar|calendar|quiz|directories|to do list|design basics|what|where)\b"), "Informational",
  REGEXMATCH(C2, "(?i)\b(buy|purchase|order|price|quote|for sale|sale|deal|offers|discount|coupon|promo code|voucher|bargain|cost|pricing|cheap|affordable|expensive|premium|near me|online|e-commerce|shopping cart|checkout|shop|store|retailer|dealer|supplier|marketplace|auction|bid|buy now|pay|payment|finance|leasing|rent|rental|subscribe|subscription|free trial|book|reserve|enquire|inquiry|estimate|cost calculator|shipping|delivery|available|in stock|out of stock|pre-order|backorder|special offer|limited time offer|flash sale|clearance|wholesale|bulk buy|group buy|membership|reward|loyalty|gift card|cashback|trade-in|renew|secure checkout)\b"), "Transactional",
  REGEXMATCH(C2, "(?i)\b(best|top|vs|versus|review|reviews|compare|comparison|ratings|recommendation|buyer's guide|alternatives?|features?|benefits?|cost-effective|affordable|premium|quality|deal|offers|savings|discount|value|choice|selection|rankings|user ratings|testimonials|feedback|pros|cons|evaluation|assessment|analysis|benchmark|brand|models|versions|upgrade|update|launch|new|latest|innovative|technology|solutions|providers|vendors|suppliers|applications|software|platform|systems|tools|equipment|machinery|accessories|devices|gadgets|electronics|appliances|products|materials|suppli\w*|kit|package|bundle)\b"), "Commercial",
  REGEXMATCH(C2, "(?i)\b(Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming|London|Edinburgh|Manchester|Birmingham|Glasgow|Liverpool|Bristol|Newcastle|Leeds|Belfast|Cardiff|Sheffield|Nottingham|Leicester|Southampton|Brighton|Plymouth|Stoke-on-Trent|Wolverhampton|Derby)\b"), "Localised",
  REGEXMATCH(C2, "(?i)\b(login|log in|sign in|homepage|website|facebook|youtube|gmail|instagram|twitter|wikipedia)\b|www\.|\.(com|org|net|edu|gov|co\.uk|uk)\b"), "Navigational"
), "Unclassified")

Order matters in a first-match formula. A keyword like “best crm pricing” contains both a commercial and a transactional word, and it gets whichever label comes first in the list. If you’d rather see every intent a keyword signals, there’s a multi-label version at the end of this section.

I encourage you to also familiarise yourself with my linked resources if you want to learn more about how to do search intent classification better, or international search intent classification, or classification in different industries. On MLforSEO, you’ll also find a comparison of rule-based and machine learning approaches to search intent classification. I also recommend following the content that Veruska Anconitano publishes on search intent, and specifically on implicit search intent.

1. How to label keywords for informational search intent in Google Sheets

This category is for queries where the user is looking for information. Keywords like “how,” “why,” “does,” “which,” “when,” “who,” “guide,” “tutorial,” “learn,” “examples,” “resource,” “ideas,” and “tips” indicate that the user is seeking knowledge or understanding.

This list focuses on keywords that are widely applicable for informational search intent, helping to ensure that content is categorised accurately when these terms are present. It maintains a broad coverage of terms related to learning, problem-solving, decision-making, and understanding concepts, which are core aspects of informational intent. (In the 2026 version I’ve added “what” and “where”, which the original list was missing.)

=IF(REGEXMATCH(C2, "(?i)\b(how|why|does|which|when|who|guide|tutorial|learn|examples|resource|ideas|tips|difference between|strategies|ways|example|directory|news|basics|concepts|troubleshoot|resolve|risks|beginners|upgrade|improve|optimize|case study|best practices|templates|pros and cons|analysis|articles|audit|benefits|blogs|build|calculate|campaign|choose|common questions|consider|cost|create|definition|definitive guide|earn|easy steps|essential|events|explainer video|factors|find|fix|grow|hacks|how to|ideal|importance of|increase|infographic|information|meaning|measure|metrics|mistakes|myths|opportunities|popular|practices|questions|remove|set up|shortcuts|stats|strategy|study|successful|tactics|techniques|terms|things to know|time|trends|tricks|types|updates|well explained|tutorials|framework|checklist|process|plan|test|glossary|sample|survey|exercises|report|certification|webinar|calendar|quiz|directories|to do list|design basics|what|where)\b"), "Informational Intent", "Other")

2. How to label keywords for commercial search intent in Google Sheets

This category is for queries indicating the user is in the consideration phase, looking for options, reviews, or comparisons. Keywords like “best,” “top,” “vs,” “review,” and “comparison” indicate commercial intent. Some words, like “affordable” or “deal,” appear in both the transactional and commercial lists: their context in a search query could lean towards either, depending on the other words they’re combined with.

For commercial intent, keywords are chosen to identify when users are researching products or services before making a purchase decision. This intent captures users comparing features, prices, or brands, seeking reviews, or looking for the best options available. The expanded list includes terms like “ratings,” “buyer’s guide,” and “pros and cons,” aiming to encompass all stages of the pre-purchase research process. By covering a wide range of research-related activities, the list helps in accurately categorising queries where the user’s primary goal is gathering information to make an informed purchase decision.

=IF(REGEXMATCH(C2, "(?i)\b(best|top|vs|versus|review|reviews|compare|comparison|ratings|recommendation|buyer's guide|alternatives?|features?|benefits?|cost-effective|affordable|premium|quality|deal|offers|savings|discount|value|choice|selection|rankings|user ratings|testimonials|feedback|pros|cons|evaluation|assessment|analysis|benchmark|brand|models|versions|upgrade|update|launch|new|latest|innovative|technology|solutions|providers|vendors|suppliers|applications|software|platform|systems|tools|equipment|machinery|accessories|devices|gadgets|electronics|appliances|products|materials|suppli\w*|kit|package|bundle)\b"), "Commercial Intent", "Other")

A small regex fix in this one: the 2024 list used alternative* and feature* as if * were a wildcard. In regex it means “zero or more of the previous character”, so it only worked by accident. They’re now alternatives? and features? (with or without the “s”), and suppli\w* catches supplies, supplier and suppliers.

3. How to label keywords for transactional search intent in Google Sheets

This category includes queries where the user’s intent is to perform a transaction, which could be purchasing a product or signing up for a service. Keywords such as “buy,” “price,” “cheap,” “expensive,” “near me,” “coupon,” “order,” “purchase,” and “pricing” suggest this intent.

The selection of transactional intent keywords focuses on pinpointing moments when a user is ready to make a purchase or is inquiring about the purchase process. Keywords such as “buy,” “quote,” “for sale,” and “checkout” directly indicate the user’s readiness to engage in a transaction. This intent is crucial for identifying users at the final stage of the buying funnel, capturing a wide array of transaction-related terms to ensure comprehensive coverage of potential purchase-related searches. The expanded list aims to cater to various transaction scenarios, from online shopping to inquiring about services.

=IF(REGEXMATCH(C2, "(?i)\b(buy|purchase|order|price|quote|for sale|sale|deal|offers|discount|coupon|promo code|voucher|bargain|cost|pricing|cheap|affordable|expensive|premium|near me|online|e-commerce|shopping cart|checkout|shop|store|retailer|dealer|supplier|marketplace|auction|bid|buy now|pay|payment|finance|leasing|rent|rental|subscribe|subscription|free trial|book|reserve|enquire|inquiry|estimate|cost calculator|shipping|delivery|available|in stock|out of stock|pre-order|backorder|special offer|limited time offer|flash sale|clearance|wholesale|bulk buy|group buy|membership|reward|loyalty|gift card|cashback|trade-in|renew|secure checkout)\b"), "Transactional Intent", "Other")

4. How to label keywords for localised intent in Google Sheets

Label keywords by US state to identify localised intent

For searches that demonstrate a desire to find information or services specific to a location in the United States, you can create a formula to detect “Localised Intent – US” by incorporating the full names of all 50 states. This allows you to categorise queries that include state names, indicating the user is looking for something within that specific state.

=IF(REGEXMATCH(C2, "(?i)\b(Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming)\b"), "Localised Intent - US", "Other")

Watch out for state names that mean other things: “Georgia” is also a country, “Washington” is also DC, and “New York” is both a state and a city. Whole-word matching at least stops “Maine” from matching inside “remaining”.

Label keywords by UK city to identify localised intent

To detect “Localised Intent – UK,” focus on incorporating major city names. This formula helps identify searches that are specific to locations within the United Kingdom, suggesting that the user’s intent is tied to finding local information, products, or services.

=IF(REGEXMATCH(C2, "(?i)\b(London|Edinburgh|Manchester|Birmingham|Glasgow|Liverpool|Bristol|Newcastle|Leeds|Belfast|Cardiff|Sheffield|Nottingham|Leicester|Southampton|Brighton|Plymouth|Stoke-on-Trent|Wolverhampton|Derby)\b"), "Localised Intent - UK", "Other")

Improve this list by adding all UK cities. You can get them from here: List of cities (HTML), GOV.UK, or any other CSV/downloadable list of all cities. In 2024, I suggested concatenating the cities with a “|” to build the formula. Today there’s a tidier way: paste the cities into their own tab (one per row) and let TEXTJOIN build the pattern for you, so the list stays editable:

=IF(REGEXMATCH(C2, "(?i)\b(" & TEXTJOIN("|", TRUE, Cities!A2:A) & ")\b"), "Localised Intent - UK", "Other")

5. How to label keywords for navigational intent in Google Sheets

Navigational intent refers to searches made with the aim of finding a specific website or page online. Users with navigational intent typically already know where they want to go and use search engines to get there directly. To detect navigational intent, include common terms associated with website navigation, as well as specific domain names, if applicable to your context.

=IF(REGEXMATCH(C2, "(?i)\b(login|log in|sign in|homepage|website|facebook|youtube|gmail|instagram|twitter|wikipedia)\b|www\.|\.(com|org|net|edu|gov|co\.uk|uk)\b"), "Navigational Intent", "Other")

This can also be improved by adding your brand names and variations, or your competitors’ brand names. Keep an eye on “website”, too: “website builder” is a commercial query, not a navigational one.

Bonus: return every intent a keyword signals, from a rules tab

Keyword lists this long are a pain to edit inside a formula. My favourite 2026 upgrade is to move them into a Rules tab (column A: the label, column B: the pipe-separated words) and use one formula for the whole keyword column. FILTER keeps every label whose words appear in the keyword, TEXTJOIN lists them, and BYROW with LAMBDA runs it for every row from a single cell:

=BYROW(C2:C, LAMBDA(kw, IF(kw="", ,
  IFERROR(TEXTJOIN(", ", TRUE,
    FILTER(Rules!A2:A, Rules!B2:B<>"", REGEXMATCH(kw, "(?i)\b(" & Rules!B2:B & ")\b"))),
  "Unclassified"))))

“best crm pricing” now comes back as “Commercial, Transactional”, which is often closer to the truth than a single label. A ready-made Rules tab with all the lists from this guide is in the code hub (rules_intent.csv): import it into a tab called Rules and you’re done.

6. How to use SERP features as search intent signals in Google Sheets

Starting from the SERP features that are returned by Semrush (you can find the equivalent documentation for Ahrefs, or the keyword research tool you are using), you can create a formula to analyse the SERP features column from your export, and classify the search results based on the features that Google is showing.

To categorise the SERP feature names based on user intent and create a formula that returns all possible labels for a keyword, we can divide the features into several intent categories. For instance:

• Informational intent: SERP features like featured snippet, People also ask, instant answer, and (new in 2026) AI Overview.
• Navigational intent: SERP features like sitelinks and the Knowledge Graph.
• Transactional intent: SERP features like the local pack, shopping ads, local teaser pack, shopping results and popular products.
• Commercial intent: SERP features like top ads and bottom ads.
• Engagement/entertainment intent: SERP features like reviews, video, top stories, images, Twitter/X, featured video, and discussions and forums.

=LET(labels, TEXTJOIN(", ", TRUE,
  IF(REGEXMATCH(J2, "(?i)Featured snippet|People also ask|Instant answer|Knowledge panel|Carousel|Thumbnail|Video preview|AI overview"), "Informational Intent", ),
  IF(REGEXMATCH(J2, "(?i)Site ?links|Knowledge Graph"), "Navigational Intent", ),
  IF(REGEXMATCH(J2, "(?i)Local pack|Shopping ads|Local teaser pack|Shopping results|Popular products"), "Transactional Intent", ),
  IF(REGEXMATCH(J2, "(?i)Top ads|Bottom ads|AdWords top|AdWords bottom"), "Commercial Intent", ),
  IF(REGEXMATCH(J2, "(?i)Reviews|Video|Top stories|Images|Twitter|Featured video|Image pack|Discussions and forums"), "Engagement/Entertainment Intent", )),
  IF(labels="", "No SERP feature signal", labels))

Three changes from the 2024 version. It uses TEXTJOIN(", ", TRUE, ...) instead of JOIN, because JOIN keeps the empty results and gave you output like “, , Informational Intent, ,”. Site ?links matches both Semrush’s “Site links” and Ahrefs’ “Sitelinks”. And it knows about AI Overviews, which Semrush now reports as a SERP feature.

This alone would not be exhaustive or very helpful for assigning intent, but combined with the previous classification (based on keywords), it can be very helpful. If you’d like to take this further in Python, there’s a free rule-based notebook for search intent classification from SERP features in the MLforSEO resource library.

Note that in the formula above, cell J2 refers to the SERP features field from your keyword export from your preferred tool.

Google Sheets formulas to help with SEO keyword research: opportunity mapping and scope

In this section, I’ll share a few other formulas I use in Google Sheets to help organise your keyword universe a bit better with keyword labels.

7. How to split keywords based on brand type (branded versus non-branded)

Here’s a formula for a custom brand label field in Google Sheets. It evaluates the content of a cell to determine whether it matches any of a list of specified patterns: these could be the name of a brand, variations and misspellings of that brand name, or names and variations of competitor brands. In the 2026 version, I split your own brand from your competitors’, because they usually call for very different actions:

=IFS(
  REGEXMATCH(C2, "(?i){brand name}|{brand variations and misspellings}"), "Branded - own",
  REGEXMATCH(C2, "(?i){competitor brand names and variations}"), "Branded - competitor",
  TRUE, "Non-branded")

Replace the text in curly brackets (and the brackets) with your own pipe-separated lists, for example mlforseo|ml for seo|mlfor seo.

8. How to identify prominent content types Google is featuring based on the SERP features

You can also use the SERP features to get a rough overview of the type of features that Google is showing in the SERP. This can help guide the content you need to create to have a better ranking chance for the user queries you aim to appear for. Here’s a possible split of SERP features, indicating different content types:

• Videos: grouping “Video” and “Featured video” to highlight video content.
• Images: including “Images”, “Image pack”, and “Thumbnail” to capture image-related features.
• Ads: consolidating all ad-related features, such as “Top ads”, “Bottom ads”, “AdWords top”, “AdWords bottom”, and “Shopping ads”.
• AI features (new): “AI overview” and the other AI answer features some tools now report.
• Informational features: features like “Featured snippet”, “People also ask”, “Instant answer”, “Knowledge panel”, “Carousel”, and “Video preview” that provide direct information.
• Navigational features: “Site links” and “Knowledge Graph” are aimed at helping users navigate to specific sites or get concise information quickly.
• Local features: focused on local search results, like “Local pack”, “Local teaser pack”, and “Shopping results”.
• Social/news features: categories such as “Reviews”, “Top stories”, “Twitter” and “Discussions and forums” represent content that is either user-generated or relates to current news and social media.

And here’s how the formula for doing that in Google Sheets might look:

=LET(labels, TEXTJOIN(", ", TRUE,
  IF(REGEXMATCH(J2, "(?i)Video|Featured video"), "Videos", ),
  IF(REGEXMATCH(J2, "(?i)Images|Image pack|Thumbnail"), "Images", ),
  IF(REGEXMATCH(J2, "(?i)Top ads|Bottom ads|AdWords top|AdWords bottom|Shopping ads"), "Ads", ),
  IF(REGEXMATCH(J2, "(?i)AI overview|AI Mode|AI summary"), "AI features", ),
  IF(REGEXMATCH(J2, "(?i)Featured snippet|People also ask|Instant answer|Knowledge panel|Carousel|Video preview"), "Informational Features", ),
  IF(REGEXMATCH(J2, "(?i)Site ?links|Knowledge Graph"), "Navigational Features", ),
  IF(REGEXMATCH(J2, "(?i)Local pack|Local teaser pack|Shopping results"), "Local Features", ),
  IF(REGEXMATCH(J2, "(?i)Reviews|Top stories|Twitter|Discussions and forums"), "Social/News Features", )),
  IF(labels="", "No features", labels))

If a keyword shows a video and an AI Overview, that’s a strong hint that a well-captioned tutorial video could earn you a citation as well as a ranking. For a deeper dive, see what SERP feature analysis is and how to use it for smarter keyword research.

Bonus: pull question keywords into their own list (with their volume and difficulty)

Question keywords are some of the most useful rows in any export: they tell you almost directly what content people want on a topic, in their own words (which often isn’t the language you’d use). Labelling them is good, but when I’m planning content I want them pulled out into their own list. Say your keyword tool’s export lives in a tab called Export, with the keyword in column A, then search volume and keyword difficulty in B and C. One FILTER returns every question keyword together with its metrics:

=FILTER(Export!A2:C, REGEXMATCH(Export!A2:A, "(?i)\b(who|what|where|when|why|how|which|can|does|should)\b"))

When I first wrote this up (a Semrush and Google Sheets keyword research tutorial), I filtered the keyword column on its own and then matched the difficulty back with a VLOOKUP. Returning all three columns in one go skips that step. If you’re hand-picking opportunities into a separate list instead, bring the metrics across with XLOOKUP, which doesn’t need the keyword to be the left-most column the way VLOOKUP does:

=XLOOKUP(A2, Export!A:A, Export!C:C, "not in export")

Semrush’s Keyword Magic Tool and Ahrefs’ Keywords Explorer both have a Questions filter built in, so for a single seed keyword you can filter before you export. The formula earns its place when you’ve stacked several seed exports into one tab, or when you’re mining your own Search Console queries for the questions people already find you for.

Google Sheets formulas to help with SEO content analysis

9. How to classify pages per site section from a URL in Google Sheets

When managing a large website, especially one run by different teams each overseeing separate segments, it’s crucial to dissect the data by page sections. This division becomes particularly handy for pinpointing technical issues, like those affecting Core Web Vitals performance, which might stem from site-wide images or specific section templates.

In Google Sheets, creating a formula to categorise page URLs by their sections can greatly aid in this analysis. Here’s how you can craft a formula to classify URLs into different categories, such as blog posts, white papers, or branded pages. This method can be especially useful in identifying and addressing website performance issues.

=IFS(
  REGEXMATCH(A2, "/blog/"), "Blog Posts",
  REGEXMATCH(A2, "/white-papers/"), "White papers",
  REGEXMATCH(A2, "/news/"), "News",
  REGEXMATCH(A2, "/case-studies/"), "Case studies",
  REGEXMATCH(A2, "/insights/"), "Insights",
  REGEXMATCH(A2, "/(careers|about-us|contact|job-opportunities|clients|reviews|our-work|our-team|join-team)(/|$)"), "Branded Pages",
  REGEXMATCH(A2, "/(services?|expertise|hospitality-travel-tourism|economic-development|energy|higher-education|technology|financial-services)(/|$)"), "Services Pages",
  REGEXMATCH(A2, "/tag/"), "Tag Pages",
  REGEXMATCH(A2, "/page/[0-9]+"), "Paginated",
  TRUE, "Unclassified")

The 2024 version was a stack of nested IFs matching plain words, so “tag” also matched “/vintage-cars/”. This one uses IFS (one condition per line, much easier to read) and anchors each pattern to the URL path, like /tag/. Again, the first match wins, so put the most specific sections first.

If you manage lots of sections, keep them in a Sections tab (label in column A, regex in column B) instead, and look the URL up against it. XLOOKUP finds the first pattern that matches:

=XLOOKUP(TRUE, ARRAYFORMULA(REGEXMATCH(A2, Sections!B2:B20)), Sections!A2:A20, "Unclassified")

Either way, you get a segmented view of your website’s structure. This segmentation can be pivotal for performance analysis or SEO optimisation, giving you a detailed breakdown of your site’s various sections alongside other metrics you choose to monitor, such as clicks, impressions, or sessions.

10. How to label URLs based on subdirectory language in Google Sheets

When handling an international website with pages in various languages, it’s crucial to analyse performance data separately for each language version. To facilitate this in Google Sheets, you can create a formula that categorises URLs by language based on their path.

The aim is to provide user-friendly labels for each language version of the URLs. This enables users to filter the data accordingly and view performance metrics, such as clicks, impressions, or sessions, for each language separately, aiding in targeted analysis and decision-making.

In 2024, I did this with one nested IF per language, assuming the URL is in cell A2:

=IF(REGEXMATCH(A2, "/es/"), "Spanish",
IF(REGEXMATCH(A2, "/fr/"), "French",
IF(REGEXMATCH(A2, "/de/"), "German",
IF(REGEXMATCH(A2, "/it/"), "Italian",
"English"))))

This formula checks the content of cell A2 for specific substrings that indicate the language of the page (e.g. “/es/” for Spanish). It assigns a label based on the first match found. If no language-specific substring is detected, it defaults to labelling the URL as “English.”

It still works. The 2026 version extracts the language folder once with REGEXEXTRACT (it also catches regional folders like /de-at/), stores it with LET, and maps it with SWITCH, so adding a language means adding two words, not another nested IF (of course, you can add as many languages as you wish, based on your knowledge of the website):

=LET(code, IFERROR(REGEXEXTRACT(LOWER(A2), "^https?://[^/]+/([a-z]{2}(?:-[a-z]{2})?)(?:/|$)"), ""),
  SWITCH(LEFT(code, 2), "es", "Spanish", "fr", "French", "de", "German", "it", "Italian", "English"))

One thing to watch: two-letter folders that aren’t languages (/us/, /uk/, or a section called /ai/) will be picked up as language codes. They’ll fall through to “English” unless you add them to the SWITCH.

11. How to classify keyword rankings per opportunity bucket in Google Sheets

This method allows for a comprehensive view of ranking performance across different tiers of keyword positions, from “money” keywords that rank in the top positions to those that might require significant improvements. Here’s a consolidated Google Sheets formula that categorises keyword positions into distinct buckets, based on opportunity and action needed:

=IFS(
  NOT(ISNUMBER(A2)), "Not ranked",
  A2 < 1, "Not ranked",
  A2 <= 3, "Money Keywords - Protect (1-3)",
  A2 <= 10, "High-Potential Keywords - Move to top (4-10)",
  A2 <= 20, "Striking Distance Keywords - Prioritise improvements (11-20)",
  A2 <= 50, "Need Improvements (21-50)",
  A2 <= 100, "Low Ranked - Heavy content edits needed (51-100)",
  TRUE, "Not ranked")

Key points:

• Replace A2 with the cell that contains the query’s rank.
• This formula applies a tiered approach to categorise rankings, providing labels that reflect each keyword’s performance level.
• It’s designed for easy integration into Google Sheets, enabling direct application to datasets derived from various ranking tools or Google Search Console.
• Fixed in 2026: the original used ranges like AND(A2>=1, A2<=3) and AND(A2>=4, A2<=10). That works for whole-number positions from a rank tracker, but Search Console reports average positions like 3.4 or 10.6, which fell into the gaps and came back as “Not ranked”. Checking upper bounds only, in order, closes the gaps.

This approach simplifies the classification of keyword rankings into actionable categories. Adapt the formula to the specific ranges you deem relevant for your analysis or reporting needs.

By categorising keywords into these buckets, you can quickly assess the distribution of your site’s rankings, identify areas of strength, and pinpoint opportunities for optimisation.

While primarily for classification, the output of this formula can also be used to enhance data visualisation in Google Sheets (or a Data Studio dashboard), facilitating a clearer understanding of ranking distributions across your website’s keywords.

12. How to extract the brand name from a URL in Google Sheets

When conducting large-scale competitor analysis or evaluating search engine results page (SERP) rankings, a crucial step is simplifying your data for better insights. A common method involves distilling URLs to their respective brand names to assess the share of voice among competitors effectively.

To accomplish this in Google Sheets, you can use a formula to extract the brand name from a given URL. This process involves stripping the protocol (http or https) and the www prefix, then isolating the brand name from the URL.

Assuming the URL is located in cell A2, here’s the formula I shared in 2024:

=TRIM(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2, "https?://", ""), "^www\.", ""), "([^\.]+)"))

This formula works as follows:

1. REGEXREPLACE(A2, "https?://", ""): removes the http:// or https:// part of the URL.
2. REGEXREPLACE(..., "^www\.", ""): removes the www. part, if present.
3. REGEXEXTRACT(..., "([^\.]+)"): extracts the brand name, which is typically the first segment of the URL following the www.. This is done by capturing characters until the first dot.

The catch is in step 3: “the first segment” is only the brand when there’s no subdomain. For blog.hubspot.com it returns “blog”, and for news.bbc.co.uk it returns “news”. The 2026 version takes the host name, then grabs the part just before the domain ending (including two-part endings like co.uk and com.au):

=LET(host, REGEXEXTRACT(LOWER(A2), "^(?:https?://)?(?:www\.)?([^/:?#]+)"),
  IFERROR(REGEXEXTRACT(host, "([^.]+)\.(?:(?:co|com|org|net|gov|ac|edu)\.[a-z]{2}|[a-z]{2,})$"), host))

The result is the brand name, which you can then use for further analysis, such as visualising the share of voice per competitor in your dataset, or even counting the number of pages ranked per competitor for your keyword set. If you need perfect accuracy across every country-code domain, a public suffix list is the robust answer, but for SERP analysis this gets you 99% of the way.

Level up: one formula for the whole column

All of the formulas above work on one row at a time: paste into row 2, drag down. Google Sheets now has a few functions that make this cleaner.

• MAP and BYROW with LAMBDA run a formula for every row from a single cell, so new rows are labelled automatically and nobody “forgets to drag it down”. Wrap any single-cell formula from this guide like this:

=MAP(C2:C, LAMBDA(kw, IF(kw="", , IF(REGEXMATCH(kw, "(?i)\b(how|why|guide|tutorial)\b"), "Informational Intent", "Other"))))

• ARRAYFORMULA does the same for simpler formulas. REGEXMATCH, IF and IFS all work inside it, e.g. =ARRAYFORMULA(IF(C2:C="", , IF(REGEXMATCH(C2:C, "(?i)\b(how|why)\b"), "Informational Intent", "Other"))).
• LET names a value once and reuses it, which is how the brand and language formulas stay readable.
• Named functions (Data > Named functions) let you save any of these as your own function. Save the intent formula as INTENT(keyword), and your whole team can type =INTENT(C2) instead of copying a 900-character regex.

What about Google Sheets’ AI() function?

Since mid-2025, Google Sheets has had an AI() function powered by Gemini, on eligible Workspace and Google AI Pro/Ultra plans. You give it a prompt and a cell, e.g. =AI("Classify the search intent of this keyword as informational, commercial, transactional or navigational", C2), and it returns a label. It’s genuinely handy for the messy long tail that no keyword list will ever cover.

But I wouldn’t swap the regex formulas for it. It processes up to 200 cells at a time, it doesn’t recalculate when your data changes (you refresh it manually), and the same keyword can get a different label on a different day. Use rule-based formulas for anything that feeds a report or a dashboard, where you need the same answer every time, and use AI() (or a proper ML classification model) to explore what the rules miss.

Grab the copyable formula sheet

Every formula in this guide (plus the rules tabs, sample data and the tests I used to check them) is in the MLforSEO code hub. Open FORMULAS.md to copy them, or import formulas.csv into a Google Sheet as a reference tab. For more ready-made spreadsheets, browse the Google Sheets templates in the MLforSEO resource library.

Want to contribute a formula to this list?

If you want to contribute a formula to this list, simply message me on LinkedIn or X (Twitter), and I’ll add it in as a contribution, with the appropriate accreditation.

Frequently asked questions

Is REGEXMATCH in Google Sheets case-sensitive?

Yes. REGEXMATCH(C2, "how") won’t match “How to…”. Add (?i) at the start of the pattern to make it case-insensitive, e.g. REGEXMATCH(C2, "(?i)how"), or wrap the cell in LOWER().

How do I match whole words only in Google Sheets regex?

Put \b (a word boundary) on both sides of the word or group: "(?i)\b(how|why|guide)\b". Without it, “how” also matches “show” and “top” matches “laptop”. Google Sheets uses Google’s RE2 regex engine, which supports \b and (?i) but not lookaheads or lookbehinds.

How do I classify search intent in Google Sheets?

List the words that signal each intent (e.g. “how, guide, tips” for informational; “buy, price, coupon” for transactional), then use REGEXMATCH inside IFS to return the first matching label, or FILTER and TEXTJOIN to return all matching labels. Add the SERP features from your keyword tool’s export as a second signal. For better accuracy at scale, move to a machine learning approach.

Why does JOIN leave extra commas in my Google Sheets output?

JOIN includes empty values, so JOIN(", ", "A", "", "B") returns “A, , B”. Use TEXTJOIN(", ", TRUE, ...) instead: the TRUE tells it to skip empty values.

Can I apply one formula to a whole column in Google Sheets?

Yes: wrap it in ARRAYFORMULA, or use MAP or BYROW with a LAMBDA, e.g. =MAP(C2:C, LAMBDA(kw, IF(kw="", , your_formula_using_kw))). The formula lives in one cell and labels every row, including new ones.

Should I use the AI() function instead of regex formulas for SEO labelling?

Use both, for different jobs. Regex formulas are deterministic, instant and free, which is what you want for reporting. The AI() function is better at fuzzy, long-tail cases but can return different answers for the same input, doesn’t recalculate automatically and works on up to 200 cells at a time.

Related on MLforSEO

• What is search intent in SEO: a practical guide to using it in semantic keyword research
• How to structure and categorise keyword data for semantic analysis
• Fuzzy Matching for SEO Use Cases (Google Sheets template)
• Glossary: Search intent, Micro-intent, AI Overview