Excel Formulas to Clean URL Parameters and UTM Tracking Codes

Marketers frequently struggle with cluttered Excel reports bloated by messy UTM tracking codes that distort campaign analysis. Before evaluating performance yields from standard marketing funding sources, analysts must first isolate clean, baseline destination URLs.

Deploying a dynamic Excel formula grants analytics teams the immediate power to audit traffic sources accurately. However, this method carries the stipulation that your dataset must utilize consistent query delimiters, like the standard question mark. For instance, cleanly isolating the root path from domain.com/landing?utm_source=newsletter provides pristine categorization. Below, we present the precise formula configurations to strip tracking parameters from your reports.

In digital marketing, SEO, and web analytics, tracking parameters are a double-edged sword. On one hand, parameters like UTM tags (utm_source, utm_medium, utm_campaign), Facebook Click IDs (fbclid), and Google Click IDs (gclid) are essential for tracking the performance of your marketing campaigns. On the other hand, when it comes to analyzing site data, these parameters clutter your reports, skewing your page-level analysis.

For instance, your web analytics platform or raw database might list the same landing page across dozens of different rows because of unique tracking codes:

• https://example.com/landing-page?utm_source=newsletter& utm_medium=email
• https://example.com/landing-page?utm_source=facebook& utm_medium=cpc& fbclid=12345
• https://example.com/landing-page?gclid=67890

To your spreadsheet, these are three completely different URLs. If you want to run a Pivot Table to see total page views or conversions for https://example.com/landing-page, your data will be fragmented. To perform accurate data matching with functions like XLOOKUP or VLOOKUP, you need a way to strip away these tracking codes and clean up your URLs.

In this guide, we will explore several Excel formulas to clean URL parameters, ranging from classic formulas compatible with older Excel versions to modern dynamic array solutions in Excel 365.

The Logic Behind URL Parameter Removal

Before diving into the formulas, it helps to understand how URLs are structured. In standard web syntax, query parameters are separated from the main page path by a question mark (?). Everything to the left of the question mark is the base URL (the protocol, domain, and path). Everything to the right of the question mark consists of key-value pairs representing the query parameters, separated by ampersands (& ).

Therefore, to clean a URL of all tracking codes and query parameters, our goal is simple: locate the position of the question mark (?) and extract only the characters that come before it. If there is no question mark in the URL, the formula should return the original URL unchanged.

Method 1: The Classic Excel Formula (Universal Compatibility)

If you are using an older version of Excel (Excel 2019, 2016, 2013, or earlier), or if you need your spreadsheet to be highly compatible across different spreadsheet processors like Google Sheets or LibreOffice, you can use a combination of LEFT, FIND, and error-handling functions.

The Traditional Formula with IFERROR

Assuming your messy URL is in cell A2, enter the following formula in cell B2:

=IFERROR(LEFT(A2, FIND("?", A2) - 1), A2)

How this formula works step-by-step:

1. FIND("?", A2): This searches for the position of the question mark in the URL string. If the URL is https://example.com/page?utm=123, the question mark is at character position 25.
2. - 1: We subtract 1 from the position of the question mark because we want to stop extracting text right before the question mark. In this case, 25 minus 1 is 24.
3. LEFT(A2, 24): This extracts the first 24 characters starting from the left side of the string, yielding https://example.com/page.
4. IFERROR(..., A2): If the URL does not contain a question mark (e.g., https://example.com/about), the FIND function will return a #VALUE! error. The IFERROR function catches this error and simply returns the original URL in cell A2.

The Elegant "No-Error" Classic Trick

There is an even more elegant way to write this classic formula without relying on IFERROR. By appending a question mark to the target cell within the search string, you guarantee that Excel will always find one, avoiding errors altogether:

=LEFT(A2, FIND("?", A2 &
"?") - 1)

In this variation, A2 & "?" appends a question mark to the very end of the URL. If the URL already has a question mark (e.g., example.com/?utm=1), FIND locates the original one. If the URL has no question mark (e.g., example.com), FIND locates the appended one at the very end, and LEFT returns the entire string up to that point. It is a clever, lightweight formula that works universally.

Method 2: The Modern Excel Way (Excel 365 & 2021)

If you are using Excel 365 or Excel 2021, Microsoft introduced a suite of powerful text manipulation functions that make this process incredibly straightforward. The star of the show here is TEXTBEFORE.

With TEXTBEFORE, you can extract all text before a specified; manually calculating string lengths or nesting error handlers. Assuming your URL is in cell A2, use this formula:

=TEXTBEFORE(A2, "?", , , , A2)

Understanding the TEXTBEFORE Arguments:

The syntax for TEXTBEFORE is: TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). Let's break down how we configured it:

• text (A2): The cell containing the messy URL.
• delimiter ("?"): The character we are looking for.
• The next three arguments (instance, match mode, match end) are left blank, which defaults to finding the first occurrence of the delimiter.
• if_not_found (A2): This is the final argument. It specifies what the formula should return if the delimiter (the question mark) is not found in the text. By pointing this back to A2, Excel safely returns the original URL if there are no tracking codes.

This approach is highly readable, less prone to structural errors, and executes remarkably fast on large datasets.

Method 3: Advanced Cleaning-Removing Specific Parameters Only

Sometimes, you do not want to strip *all* parameters from your URLs. You might want to retain functional parameters (like a product ID, search query, or page pagination) while stripping away marketing tracking codes like utm_source or fbclid.

For example, you want to keep https://example.com/store?product_id=9876 but get rid of &utm_campaign=winter_sale from the end of it.

To accomplish this, we can use a combination of LET, TEXTSPLIT, and TEXTJOIN in Excel 365 to selectively filter out unwanted parameters. Here is an advanced formula to clean only specific tracking parameters (in this case, utm_source, utm_medium, utm_campaign, and gclid):

=LET(
    url, A2,
    has_params, ISNUMBER(FIND("?", url)),
    IF(NOT(has_params), url,
        LET(
            base, TEXTBEFORE(url, "?"),
            query_string, TEXTAFTER(url, "?"),
            pairs, TEXTSPLIT(query_string, "&"),
            filtered_pairs, FILTER(pairs, 
                ISERR(MATCH(LEFT(pairs, SEARCH("=", pairs & "=") - 1), {"utm_source","utm_medium","utm_campaign","gclid","fbclid"}, 0))
            ),
            IF(ISERR(filtered_pairs), base, base & "?" & TEXTJOIN("&", TRUE, filtered_pairs))
        )
    )
)

How This Advanced Formula Works:

• The LET function defines variables for cleaner execution. We first check if the URL has parameters using ISNUMBER(FIND("?", url)).
• If it doesn't, we return the URL as is. If it does, we split the URL into its base and its query_string.
• We split the query string by the ampersand (&) using TEXTSPLIT to isolate each parameter key-value pair into an array.
• The FILTER function strips out any pairs where the parameter name matches our "blacklist" array: {"utm_source","utm_medium","utm_campaign","gclid","fbclid"}.
• Finally, TEXTJOIN stitches the remaining parameters back together with ampersands, attaches them to the base URL with a question mark, and returns a perfectly clean URL that retains important non-tracking parameters.

Pro-Tip: Scaling Up with Power Query

While formulas are excellent for on-the-fly calculations, they can slow down your workbook if you are processing hundreds of thousands of rows of analytics data. If you are dealing with massive marketing exports, consider using Excel's built-in tool: Power Query.

To clean URL parameters in Power Query:

1. Select your data range and go to the Data tab, then click From Sheet (or From Table/Range).
2. In the Power Query Editor, right-click the header of your URL column and select Split Column > By Delimiter.
3. Choose Custom as the delimiter type, enter a question mark (?), and select Left-most delimiter. Click OK.
4. This splits your URLs into two columns. The first column will contain the clean base URL. You can delete the second column containing the tracking parameters.
5. Rename your clean column, then click Close & Load to return the clean data to your Excel sheet.

Summary

Cleaning tracking parameters from your URLs is a foundational step in web data analysis. It prevents duplication, ensures your formulas match datasets correctly, and makes your Pivot Tables instantly readable. Depending on your version of Excel, you can use the highly compatible =LEFT(A2, FIND("?", A2 & "?") - 1) trick or the modern, elegant =TEXTBEFORE(A2, "?", , , , A2) function. For advanced use cases, the dynamic array formula and Power Query offer robust scalability to handle complex analytical demands with ease.