How to Clean Website URLs in Google Sheets

Website URLs collected from business directories, lead-generation tools, forms, and spreadsheet imports are rarely consistent. One row may contain https://www.example.com/, while another contains http://example.com/services?utm_source=email. Although both entries may belong to the same website, Google Sheets can treat them as different values.

Learning how to clean website URLs in Google Sheets helps you organize lead lists, detect duplicates, prepare CRM imports, and standardize website data. In this guide, you will learn how to remove protocols, www, tracking parameters, paths, extra spaces, fragments, and trailing slashes using practical Google Sheets formulas.

Table of Contents

1. What URL cleaning means
2. Common website URL problems
3. Best formula for extracting a clean domain
4. How to clean an entire URL column
5. URL-cleaning formulas for different purposes
6. How to validate cleaned domains
7. Recommended URL-cleaning workflow
8. Key takeaways
9. Common mistakes
10. Pro tips and best practices
11. Frequently asked questions

What Does It Mean to Clean Website URLs in Google Sheets?

Cleaning website URLs means converting different URL formats into one consistent structure. The correct structure depends on how you plan to use the data.

• Root domain: example.com
• Standard full URL: https://example.com
• Page URL without tracking: https://example.com/services

For company matching, duplicate removal, and lead-list organization, the root domain is usually the most useful format. For clickable website fields, a complete HTTPS URL may be better.

Before applying any formula, decide whether you need the company domain or the exact webpage URL. Removing /services from a URL is useful for identifying the company website, but it is not useful when the specific service page must be preserved.

Common Website URL Problems You Should Fix

Website data often contains inconsistent formatting. The most common problems include:

• Mixed http:// and https:// protocols
• Some URLs containing www and others without it
• Uppercase letters in domain names
• Leading or trailing spaces
• Tracking parameters such as ?utm_source=email
• URL fragments such as #contact
• Unnecessary page paths
• One or more trailing slashes
• Hidden or non-printable characters

Original Website URL	Clean Domain
HTTPS://WWW.Example.com/	example.com
http://example.com/services/?utm_source=email	example.com
www.example.com/#contact	example.com
example.com/pricing	example.com

Standardizing these entries makes sorting, filtering, matching, and duplicate detection more reliable.

Best Formula to Clean Website URLs in Google Sheets

Assume your original website URL is in cell A2. Enter the following formula in cell B2:

=IF(A2="","",LOWER(REGEXREPLACE(REGEXREPLACE(TRIM(CLEAN(A2)),"(?i)^https?://(www\.)?",""),"[/?#].*$","")))

This formula converts a messy website URL into a lowercase root domain.

For example:

https://www.Example.com/services/?utm_source=email

Becomes:

example.com

How This Formula Works

1. IF(A2="","",...) leaves the result blank when the original cell is empty.
2. CLEAN(A2) removes hidden, non-printable characters.
3. TRIM(...) removes unnecessary spaces.
4. The first REGEXREPLACE removes HTTP, HTTPS, and an optional www..
5. The second REGEXREPLACE removes paths, query parameters, and fragments.
6. LOWER(...) converts the final domain to lowercase.

This formula is best for lead lists, business directories, CRM data, and company-level duplicate checking.

How to Clean an Entire URL Column Automatically

If your spreadsheet contains hundreds or thousands of URLs, you do not need to copy the formula down manually. Use ARRAYFORMULA to clean the complete column.

Place this formula in cell B2:

=ARRAYFORMULA(IF(A2:A="","",LOWER(REGEXREPLACE(REGEXREPLACE(TRIM(CLEAN(A2:A)),"(?i)^https?://(www\.)?",""),"[/?#].*$",""))))

Step-by-Step Instructions

1. Place the original website URLs in column A.
2. Enter Original Website in cell A1.
3. Enter Clean Domain in cell B1.
4. Paste the ARRAYFORMULA into cell B2.
5. Check several results manually.
6. Pay special attention to URLs containing paths or tracking parameters.
7. Copy the cleaned column after confirming the results.
8. Use Paste special > Values only when fixed text is required.

Keep the original website column until you have checked the cleaned results. This allows you to restore information if a path or subdomain was removed accidentally.

Useful URL-Cleaning Formulas for Different Purposes

Not every project requires the same output. Use the formula that matches your goal.

Remove HTTP, HTTPS, and www but Keep the Page Path

=IF(A2="","",REGEXREPLACE(TRIM(CLEAN(A2)),"(?i)^https?://(www\.)?",""))

This converts:

https://www.example.com/services

Into:

example.com/services

Remove Tracking Parameters and URL Fragments

=IF(A2="","",REGEXREPLACE(TRIM(CLEAN(A2)),"[?#].*$",""))

This converts:

https://example.com/services?utm_source=email#pricing

Into:

https://example.com/services

Remove Trailing Slashes

=IF(A2="","",REGEXREPLACE(TRIM(CLEAN(A2)),"/+$",""))

This removes one or more slashes from the end of the URL while preserving the rest of the address.

Create a Standard HTTPS Root URL

=IF(A2="","","https://"&LOWER(REGEXREPLACE(REGEXREPLACE(TRIM(CLEAN(A2)),"(?i)^https?://(www\.)?",""),"[/?#].*$","")))

This converts different website formats into a complete root URL such as:

https://example.com

This formula is useful when another platform requires a complete clickable website URL rather than a plain domain.

How to Check Whether a Cleaned Domain Looks Valid

A URL-cleaning formula standardizes the text, but it does not confirm that the website is active. You can use REGEXMATCH for a basic domain-format check.

If the cleaned domain is in cell B2, use:

=IF(B2="","",REGEXMATCH(B2,"(?i)^[a-z0-9-]+(\.[a-z0-9-]+)+$"))

The formula returns:

• TRUE: The value looks like a domain.
• FALSE: The value may be incorrectly formatted.

This is only a formatting check. A domain may have the correct format but still be expired, parked, redirected, or unavailable.

Recommended Workflow for Lead Lists and Website Data

1. Preserve the original data. Keep the raw URL column or create a backup sheet.
2. Choose one format. Decide whether you need a root domain, complete HTTPS URL, or exact page URL.
3. Clean the data in a new column. Do not immediately replace the original values.
4. Review unusual entries. Check social profiles, URL shorteners, subdomains, directory listings, and malformed links.
5. Validate the cleaned domains. Use a separate formula or conditional formatting.
6. Convert formulas to values. Do this before CSV export or CRM import.
7. Remove duplicate records. After standardizing your domain column, follow this guide on How to Remove Duplicate Leads in Google Sheets.

Cleaning the website column before removing duplicates improves matching accuracy. Without standardization, www.example.com and https://example.com/ may appear as separate records.

Key Takeaways

• Choose the required output format before cleaning your data.
• Use REGEXREPLACE, TRIM, and CLEAN for consistent results.
• Use ARRAYFORMULA to process an entire column.
• Keep the original URL data until the cleaned results are verified.
• Use root domains for company matching and duplicate removal.
• Use full HTTPS URLs when clickable website links are required.

Common Mistakes to Avoid

• Removing necessary page paths: Do not extract only the domain when the exact page URL is important.
• Overwriting the original data: Always clean the URLs in a separate column first.
• Lowercasing complete page URLs: Domain names can be lowercase, but some website paths may be case-sensitive.
• Removing meaningful subdomains: A subdomain may represent a separate platform, store, or customer portal.
• Assuming TRUE means active: REGEXMATCH checks the format, not whether the website works.
• Exporting formulas instead of values: Convert formulas to values before importing data into another system.

Pro Tips and Best Practices

• Use separate columns named Original Website, Clean Domain, and Validation Status.
• Test your formula on a small group of complicated URLs first.
• Use conditional formatting to highlight FALSE validation results.
• Keep social-media profiles in a separate column from company websites.
• Review country-specific domains and subdomains before merging records.
• Export a new CSV after completing URL cleaning and duplicate removal.
• Document the chosen URL format so everyone working on the spreadsheet follows the same standard.

Frequently Asked Questions

How do I remove HTTP and HTTPS from URLs in Google Sheets?

Use the following formula:

=REGEXREPLACE(A2,"(?i)^https?://","")

It removes either HTTP or HTTPS when the protocol appears at the beginning of the URL.

How do I remove www from a website URL?

Use:

=REGEXREPLACE(A2,"(?i)^www\.","")

If the URL also contains HTTP or HTTPS, use the combined cleaning formula provided earlier.

How do I remove UTM parameters in Google Sheets?

Use:

=REGEXREPLACE(A2,"[?#].*$","")

This removes the question mark or hash symbol and everything that follows it.

How do I extract only the domain from a URL?

Use the main formula in this guide. It removes HTTP, HTTPS, www, paths, query parameters, fragments, extra spaces, and hidden characters.

Can I clean thousands of URLs with one formula?

Yes. Use the ARRAYFORMULA version to process every populated cell in a complete column automatically.

Does cleaning a URL confirm that the website is active?

No. Cleaning only standardizes the text. You must check the website separately to confirm that it loads and is still active.

Should I remove www from cleaned domains?

Removing www usually helps with company matching and duplicate detection. For a live clickable link, use the website's preferred version when it is known.

Conclusion

Understanding how to clean website URLs in Google Sheets can make lead lists, CRM imports, outreach files, and business databases significantly more accurate. The correct formula depends on whether you need a root domain, a complete HTTPS website, or a page URL without tracking parameters.

Start by preserving your original data, apply the appropriate formula in a separate column, validate the results, and convert the final formulas to values before exporting. Once your website URLs are standardized, you can remove duplicate leads more accurately and maintain a cleaner spreadsheet.

Try the formula on a small sample of your website data today, then apply it to your complete spreadsheet after checking the results.