logo
search
list

Table of Content

Get Domain From URL With One Formula
Get Domain From URL in Older WPS Builds
Get Domain From URL as a Root Domain
Get Domain From URL Counts for a Report
The Best Free Spreadsheet Alternative: WPS Office
FAQs
Summary

Get Domain From URL in WPS Spreadsheet: Three Formulas

Posted by Algirdas Jasaitis

calendar

2026-10-26

views

870

likes

4

A column of 4,000 links is useless until the domain sits in its own cell, and retyping them by hand is not an option. Put this in the next column and fill it down: =TEXTBEFORE(TEXTAFTER(A2,"//"),"/"). It strips the protocol, keeps the host, and stops at the first slash.

Get Domain From URL in WPS Spreadsheet: Three Formulas

Get Domain From URL in WPS Spreadsheet: Three Formulas

100% secure

Get Domain From URL With One Formula

The formula reads the URL in two moves: TEXTAFTER jumps past https:// or http://, and TEXTBEFORE stops at the first /. Whatever sits between those two points is the host name, including any subdomain.

  1. Put the links in column A, starting at A2, with a header in A1. Keep the full URLs — the formula needs the protocol to find its starting point.

  2. Click B2 and type the formula: =TEXTBEFORE(TEXTAFTER(A2,"//"),"/")

  3. Press Enter and check the first result by eye. If B2 shows shop.example.com for https://shop.example.com/products/12, the pattern is right.

  4. Double-click the fill handle in the corner of B2. WPS Spreadsheet copies the formula down to the last row of data, and the references adjust on their own.

  5. Scan for errors before you build anything on top. A #VALUE! cell usually means one row holds a link without //, and that row needs the fallback in the next section.

    Extract domain from URL formula

    Extract domain from URL formula

If a few rows are plain domains without a protocol, prefix them inside the formula instead of editing the data: =TEXTBEFORE(TEXTAFTER("//"&A2,"//"),"/") returns example.org for both example.org/about and https://example.org/about.

WordPowerPointExcelPDF
Use Word, Excel, and PPT for FREE

Get Domain From URL in Older WPS Builds

TEXTAFTER and TEXTBEFORE are relatively new functions. On an older WPS build, or in a file that has to open in an older Excel, they return #NAME? and the whole column breaks. The classic version works everywhere and costs one nested formula:

=LEFT(SUBSTITUTE(SUBSTITUTE(A2,"https://",""),"http://",""),FIND("/",SUBSTITUTE(SUBSTITUTE(A2,"https://",""),"http://","")&"/")-1)

  • Why the nested SUBSTITUTE: it removes the protocol first, so LEFT starts counting at the domain instead of at https.

  • Why the &"/" at the end: a URL with no trailing slash would otherwise return an error. Appending a slash guarantees FIND always has something to find.

  • What it cannot do: it keeps www. and any port number, because those are part of the host. Clean them in the next column rather than inside this formula.

  • Copy the result as values before sharing. Select the column, copy, then paste as values so a colleague on a different build sees text rather than a formula error.

100% secure

Get Domain From URL as a Root Domain

Reporting usually needs the registrable domain, not the host: shop.example.com, www.example.com and blog.example.com should all roll up to example.com. Three small formulas do that.

  • Last two labels: =TEXTAFTER(B2,".",-2) turns shop.example.com into example.com. The negative instance number counts dots from the right.

  • Two-part suffixes: the same formula returns co.uk for example.co.uk, which is wrong. Wrap it: =IF(ISNUMBER(SEARCH(".co.",B2)),TEXTAFTER(B2,".",-3),TEXTAFTER(B2,".",-2)) and add the suffixes you actually meet, such as .com.my or .co.id.

  • Strip www: =SUBSTITUTE(B2,"www.","") removes the prefix wherever it appears, so www.example.com and example.com finally match.

  • Drop a port: =IFERROR(TEXTBEFORE(B2,":"),B2) handles hosts copied out of a staging environment, where www.example.com:8443 would otherwise sit in its own row forever.

    Clean host and domain columns

    Clean host and domain columns

Keep the host column and the clean domain column separate. The host is what you debug with when a link looks wrong; the clean domain is what you group by. Overwriting the host loses the evidence.

WordPowerPointExcelPDF
Use Word, Excel, and PPT for FREE

Get Domain From URL Counts for a Report

Once the domain is in its own column, the analysis is one step away. Counting beats eyeballing, and it takes a single formula or a pivot table.

  1. List the domains you care about in a blank area, or generate the unique list with =UNIQUE(C2:C5000) on builds that support dynamic arrays.

  2. Count each one: =COUNTIF($C:$C,A2) beside the list returns every occurrence, including the ones you forgot were in the file.

  3. Or build a pivot table. Select the domain column, choose Insert, Pivot Table, then drag the domain into Rows and the same field into Values. WPS writes the counts without a formula.

  4. Chart the top ten only. A bar chart of 400 domains tells nobody anything; sort descending, keep ten, and the report reads in one second.

    Visits counted by domain in WPS Spreadsheet

    Visits counted by domain in WPS Spreadsheet

One practical check before you trust the numbers: search the clean domain column for the empty-looking results. A blank cell means the original URL had no host at all, and those rows will quietly shrink your totals.

100% secure

Two habits keep this column trustworthy over time. First, keep the formula in a column with a clear header such as host, not in a scratch column you will delete next week. Second, when new URLs arrive, paste them at the bottom of column A and re-fill the formula range; a mixed range where the last 200 rows have no formula is the most common reason a report undercounts.

The Best Free Spreadsheet Alternative: WPS Office

Cleaning a URL column is a five-minute job in a spreadsheet you already have, and a fifteen-minute job when the file opens with broken formulas in a different suite. WPS Spreadsheet runs the same text functions with the same syntax.

  • TEXTAFTER, TEXTBEFORE, UNIQUE and COUNTIF work the same way they do in Excel, so a formula written here travels.

  • One-click pivot tables for the counting step, with the chart on the same sheet.

  • Clean .xlsx compatibility, so a file that arrives from a client opens with its formulas and formatting intact.

  • AI formula help inside the sheet when the nesting gets deep, and no upload step before you can see the result.

  • Free for personal use on desktop, web and mobile, with WPS Spreadsheet included in the same suite as Writer and PDF.

WPS Spreadsheet main interface

WPS Spreadsheet main interface

WordPowerPointExcelPDF
Use Word, Excel, and PPT for FREE

FAQs

What is the simplest formula to get the domain from a URL?

=TEXTBEFORE(TEXTAFTER(A2,"//"),"/") returns the host name from a full URL. It strips the protocol and stops at the first slash after the domain.

Why does TEXTAFTER return #NAME? in my file?

The function does not exist in that build or file format. Use the LEFT, SUBSTITUTE and FIND combination instead, or save the file as .xlsx in a current WPS version.

How do I get example.com instead of shop.example.com?

=TEXTAFTER(B2,".",-2) returns the last two labels. For suffixes such as .co.uk, add the ISNUMBER(SEARCH(".co.",B2)) test so those rows keep three labels.

Can I remove www with the same formula?

Use =SUBSTITUTE(B2,"www.","") in a separate column. Keeping the original host makes it possible to check which rows had the prefix.

How do I count how many links come from each domain?

Use =COUNTIF($C:$C,A2) next to a list of domains, or a pivot table with the domain field in Rows and Values. Both give the same count.

Free Office Download
Word
PowerPoint
Excel
PDF
   

  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

100% secure
Algirdas Jasaitis
Algirdas Jasaitis

Summary

Get the domain from a URL with =TEXTBEFORE(TEXTAFTER(A2,"//"),"/"), and keep the classic LEFT and FIND version for older builds that do not know the newer functions.

Add two columns after the host: one for the clean domain with www removed, one for the root domain, and handle two-part suffixes such as .co.uk explicitly.

Counting is the point of the whole exercise: count with COUNTIF or a pivot table, then chart the top ten only. WPS Spreadsheet does all of it on the free tier.

100% secure


Algirdas Jasaitis

Algirdas Jasaitis

facebooktwitteryoutube

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.

Other Posts You Might Enjoy

Messages

Wildcards in Excel: How to Use *, ? and ~ (With Examples)

icon-arrowPencil

Bulk Barcode Generator: Create Hundreds of Barcodes From a Spreadsheet

icon-arrowNotifications

Percent to Goal Calculator in Excel: Formula + Free Template

icon-arrowFolder

How to Open, Convert, and Create ETT File

icon-arrowLight

How to Create an Answer Key for Tests in Excel or WPS Spreadsheet

icon-arrowCalendar

How to Make a Donut Chart: When to Use It Instead of a Pie Chart

icon-arrow