How to Clean Website URLs in Google Sheets

What Does a Clean Website URL Look Like?

A clean website URL is simply a URL that follows a consistent format and does not contain unnecessary characters, tracking parameters, spaces, or other clutter. If you're working with a large SEO, marketing, affiliate, or competitor-analysis spreadsheet, this small distinction can make a surprisingly big difference.

For example, these URLs may point to essentially the same page:

https://example.com/page

https://example.com/page/

https://www.example.com/page?utm_source=google

https://example.com/page#comments

To a spreadsheet, however, they are different text strings. Google Sheets will not automatically understand that several variations represent the same underlying URL. That's why URL normalization is useful when you're analyzing website data.

Why Should You Clean URLs?

Imagine you're sorting a box of cables. If every cable is tangled, labeled differently, and mixed together, finding the one you need takes forever. Website URLs can create the same problem in a spreadsheet.

Cleaning URLs helps you standardize your data before analyzing it. You may be working with URLs collected from Google Search results, website crawls, analytics exports, backlinks, affiliate reports, or competitor research. Once your links are sanitized, you can also follow our guide on How to Extract Domain Names from Urls to group and analyze your traffic sources by root domain.

A clean dataset can make duplicate detection, filtering, grouping, and reporting much easier.

Google Sheets includes several functions that are particularly useful for this job, including TRIM, SUBSTITUTE, LOWER, REGEXREPLACE, and REGEXEXTRACT. Google's official function documentation lists these among its text-processing functions.

Prepare Your Google Sheet

Before changing anything, make a copy of your original URL column.

Suppose your spreadsheet looks like this:

Put your original URLs in column A and perform your cleaning in column B.

Never overwrite your original data until you've checked the results. A backup column gives you an easy way to compare the original and cleaned versions.

Remove Extra Spaces From URLs

One of the easiest URL problems to fix is unwanted whitespace.

A URL copied from another application might contain a leading or trailing space. These characters can be difficult to notice visually but can interfere with formulas and comparisons.

Google Sheets' TRIM function removes leading, trailing, and repeated spaces from text.

Using the TRIM Function

If your URL is in cell A2, enter:

=TRIM(A2)

For example:

 https://example.com/page 

becomes:

https://example.com/page

Handling Hidden Spacing Problems

TRIM is useful, but it does not remove every type of whitespace. Google specifically notes that non-breaking spaces are not removed by TRIM.

For difficult imported data, you can combine CLEAN and TRIM:

=TRIM(CLEAN(A2))

CLEAN removes non-printable ASCII characters from text.

Remove Unwanted Characters

URLs collected from websites or exported from different tools sometimes contain characters you don't want in your analysis.

The SUBSTITUTE function is helpful when you know exactly what you want to remove or replace.

For example:

=SUBSTITUTE(A2," ","")

This removes regular spaces from the URL.

You can also replace specific strings. For example, to remove www.:

=SUBSTITUTE(A2,"www.","")

However, be careful. Do not blindly remove characters from URLs unless you understand how they affect the destination.

A question mark, slash, equals sign, or other character can have an important role in a URL.

Remove Tracking Parameters

Tracking parameters are one of the most common reasons URLs become messy.

You may see URLs such as:

https://example.com/product?utm_source=facebook&utm_medium=social

The utm_source and utm_medium values are useful for marketing attribution, but they may not be useful when you're trying to identify the basic page URL.

Understanding UTM Parameters

A query string begins after the ? character.

For example:

https://example.com/page?utm_source=newsletter

The main URL is:

https://example.com/page

Removing Query Strings With REGEXREPLACE

You can use:

=REGEXREPLACE(A2,"\?.*$","")

This tells Google Sheets to find a question mark and everything after it, then replace that section with nothing.

Google Sheets' REGEXREPLACE function replaces matching text using regular expressions and replaces all matching instances.

This is one of the most useful formulas for cleaning large URL lists.

Remove URL Fragments

You may also encounter URLs containing a hash symbol:

https://example.com/guide#section-3

The part after # is called a fragment.

If your goal is to compare page-level URLs, you may want to remove the fragment.

Use:

=REGEXREPLACE(A2,"#.*$","")

The result becomes:

https://example.com/guide

This can be particularly useful when a page contains many internal anchor links and you don't want every anchor to appear as a separate URL in your analysis.

Standardize HTTP and HTTPS

You may find both of these in your spreadsheet:

http://example.com/page

and:

https://example.com/page

If you're analyzing a modern website dataset, you may want to standardize URLs to HTTPS.

A simple replacement formula is:

=SUBSTITUTE(A2,"http://","https://")

However, don't assume that every HTTP URL automatically has an HTTPS equivalent. Changing the text in a spreadsheet does not verify that the destination actually works over HTTPS.

The formula only changes the text representation.

Remove www From Website URLs

Another common inconsistency is the use of www.

You might have:

https://www.example.com/page

and:

https://example.com/page

If your analysis treats these as the same domain, you can remove www.:

=SUBSTITUTE(A2,"www.","")

The result is:

https://example.com/page

When Removing www Makes Sense

This is useful when you're creating a normalized dataset for domain-level analysis.

However, remember that www.example.com and example.com can technically be configured as different hosts. URL cleaning should standardize your data for a specific analytical purpose, not rewrite URLs blindly.

Remove Trailing Slashes

Trailing slashes are another common source of apparent duplicates.

For example:

https://example.com/about

and:

https://example.com/about/

may appear separately in your spreadsheet.

If your project requires URLs without trailing slashes, you can use:

=REGEXREPLACE(A2,"/$","")

This removes a slash only when it appears at the end of the URL.

Why Trailing Slashes Create Duplicates

When you are comparing thousands of URLs, even tiny differences matter.

A spreadsheet doesn't think:

"These look almost identical."

It thinks:

"These are two different strings."

That is why normalization rules are so important before you run duplicate checks.

Convert URLs to Lowercase

You can also standardize the text using the LOWER function:

=LOWER(A2)

For example:

HTTPS://EXAMPLE.COM/ABOUT

becomes:

https://example.com/about

This can make comparisons more consistent.

However, there is an important caveat: do not assume that every URL path is case-insensitive. Domains are generally handled case-insensitively, but server configurations can treat path characters differently.

Therefore, lowercase conversion is best used when you know that your dataset and analysis rules support it.

Clean URLs With One Formula

Instead of creating a separate column for every cleaning step, you can combine several functions.

For example:

=LOWER(TRIM(CLEAN(REGEXREPLACE(REGEXREPLACE(A2,"\?.*$",""),"#.*$"))))

This formula:

1. Removes query strings.
2. Removes fragments.
3. Removes non-printable characters.
4. Removes extra spaces.
5. Converts the result to lowercase.

That's a lot of cleanup packed into one line.

The advantage is speed; the disadvantage is readability. If you're working with a team, separate columns can sometimes make troubleshooting much easier.

Clean an Entire Column Automatically

If you have hundreds or thousands of URLs, copying a formula down manually can become tedious.

Google Sheets supports ARRAYFORMULA, which can apply calculations across a range.

For example:

=ARRAYFORMULA(IF(A2:A="","",LOWER(TRIM(CLEAN(A2:A)))))

This applies the cleanup to the entire column while leaving blank rows blank.

For more complicated regex-based formulas, you may prefer a helper column or a carefully tested array formula.

Always test your formula on a small sample before applying it to a huge dataset.

Find Duplicate URLs

Once your URLs are cleaned, duplicate detection becomes much more meaningful.

Suppose your cleaned URLs are in column B. You can use:

=COUNTIF(B:B,B2)

If the result is greater than 1, the URL appears more than once.

For example:

=IF(COUNTIF(B:B,B2)>1,"Duplicate","Unique")

This creates a simple label for every row.

Using Google Sheets Cleanup Tools

Google Sheets also provides built-in cleanup features for identifying issues such as duplicates, extra spaces, and inconsistent data. Google's Smart Cleanup documentation explains how to access Cleanup Suggestions and Column Stats.

You can also use Data → Data cleanup → Remove duplicates when appropriate.

Extract the Domain From a URL

Sometimes you don't need the entire URL. You only want the domain.

For example:

https://www.example.com/blog/article

You might want:

example.com

A simple formula can be created with several text functions, but regular expressions often make this easier.

For example:

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

Google Sheets' REGEXEXTRACT function returns text matching a regular expression, making it useful for pulling specific pieces from URLs.

This is helpful for competitor research, backlink analysis, domain grouping, and content audits.

Validate Cleaned URLs

Cleaning a URL does not mean the URL is valid.

After cleaning, check for obvious problems such as:

• Missing https://
• Empty cells
• Spaces inside URLs
• Double slashes
• Broken domains
• Incomplete URLs
• Unexpected punctuation

You can use REGEXMATCH to test whether a cell follows a basic pattern. Google Sheets describes REGEXMATCH as a function that determines whether text matches a regular expression and returns TRUE or FALSE.

For example:

=REGEXMATCH(A2,"^https?://")

This checks whether the URL begins with either http:// or https://.

It's not a complete URL validator, but it is a useful first filter.

Common URL Cleaning Mistakes

One of the biggest mistakes is trying to make every URL look identical without understanding what the URL components mean.

For example, deleting everything after ? can remove useful information. Some websites use query parameters for filters, product variants, searches, pagination, or even identifying the requested resource.

Another mistake is converting every URL to lowercase without considering case-sensitive paths.

Over-Cleaning URLs

Think of URL cleaning like trimming a tree. You want to remove dead branches, not cut down the entire tree.

Keep important information unless you have a clear reason to remove it.

Removing Important Parameters

Before removing query parameters, ask:

"Are these parameters tracking information, or are they actually part of how the website delivers the content?"

That one question can prevent a lot of bad data.

Best Practices for URL Management

The best URL-cleaning workflow is consistent and reversible.

Start by keeping the original URL. Create a separate cleaned column and document exactly what your formula removes.

For example, your workflow might be:

Original URL → Trim → Remove tracking parameters → Remove fragments → Standardize protocol → Standardize hostname → Check duplicates → Validate

This gives you a repeatable process.

You can also use Google Sheets' Find and Replace tool for simpler bulk replacements. Google supports options such as regular-expression searching and replacing data across sheets.

If you're working with a large SEO project, save your cleaning formula somewhere accessible. That way, you don't have to reinvent the process every time you receive a new URL export.

A Practical URL Cleaning Formula

If you want a starting point for many ordinary URL datasets, try:

=LOWER(REGEXREPLACE(REGEXREPLACE(TRIM(CLEAN(A2)),"\?.*$",""),"#.*$",""))

This formula is designed to:

• Remove extra spaces.
• Remove non-printable ASCII characters.
• Remove query strings.
• Remove URL fragments.
• Convert the result to lowercase.

You can then add additional rules for www, trailing slashes, or protocol changes if those rules fit your project.

There is no single "perfect" URL-cleaning formula because the right formula depends on what you consider to be the canonical version of a URL.

Conclusion

Cleaning website URLs in Google Sheets doesn't have to be complicated. With a handful of functions such as TRIM, CLEAN, SUBSTITUTE, LOWER, REGEXREPLACE, and REGEXEXTRACT, you can turn a messy URL list into a much more consistent dataset.

The key is to clean with a purpose. Remove tracking parameters when they are irrelevant to your analysis, standardize formatting when appropriate, and always keep the original URLs available for comparison.

A few well-designed formulas can save hours of manual URL cleanup.

If you're working with SEO audits, backlink lists, content inventories, competitor research, or website crawls, Google Sheets can be a surprisingly powerful URL-cleaning tool.

For additional reference, Google's official documentation covers REGEXREPLACE, TRIM, CLEAN, and REGEXEXTRACT.

FAQs

1. Can Google Sheets automatically clean website URLs?

Yes. Google Sheets can automate many URL-cleaning tasks with formulas. Functions such as TRIM, CLEAN, SUBSTITUTE, LOWER, and REGEXREPLACE can be combined to create a repeatable cleanup process.

2. How do I remove UTM parameters from URLs in Google Sheets?

If the URL is in A2, you can use:

=REGEXREPLACE(A2,"\?.*$","")

This removes the question mark and everything that follows it. Before using it on a large dataset, make sure the query parameters you are removing are only tracking or other unwanted information.

3. How do I remove www from URLs in Google Sheets?

Use:

=SUBSTITUTE(A2,"www.","")

This changes https://www.example.com to https://example.com. Only use this if your analysis treats both hostnames as equivalent.

4. How do I remove a trailing slash from a URL?

Use:

=REGEXREPLACE(A2,"/$","")

This removes a slash only when it appears at the end of the URL.

5. Can I clean thousands of URLs at once?

Yes. You can apply formulas down a column or use ARRAYFORMULA for many rows. For large datasets, test the formula on a sample first and preserve the original URLs before making bulk changes.

6. Does cleaning a URL mean the website URL is valid?

No. URL cleaning only changes or standardizes the text. It does not confirm that a webpage exists, redirects correctly, or returns a successful HTTP response. Validation should be treated as a separate step.

7. What is the best Google Sheets function for URL cleaning?

There isn't one universal function. REGEXREPLACE is particularly powerful because it can remove patterns, while TRIM, CLEAN, SUBSTITUTE, and LOWER handle specific cleanup tasks. Combining them usually produces the best results.