Text & String Formulas
Extract Clean Hostname / Domain from Any URL (LEFT, FIND & SUBSTITUTE)
Strip http://, https://, leading www., port numbers (:8443), and trailing /path?query segments from messy referral URLs using sentinel-guarded FIND and SUBSTITUTE.
Why appending "/" and ":" sentinels eliminates every #VALUE! URL edge case!
SEO backlink exports and analytics referral logs mix bare domains ("https://example.com"), deep paths ("https://www.shop.example.com/cart?id=9"), HTTP links, and custom staging ports ("http://api.example.com:8080/v1"). Calling FIND("/", A2, 9) crashes with #VALUE! whenever a root URL has no trailing slash!
Appending a sentinel "/" inside FIND("/", clean_url & "/") guarantees FIND ALWAYS succeeds—even on slashless bare domains! Stripping "https://", "http://", leading "www.", anything after "/", and anything after ":" in sequence extracts clean hostnames across every URL variation!
| Raw Analytics Referral URL | Protocol & www. Stripped | Clean Hostname Returned |
|---|---|---|
| "https://www.sheets.arte-soft.com/en/guides?src=nav" | sheets.arte-soft.com/en/... | "sheets.arte-soft.com" |
| "http://api.vendor.io:8443/v2/status" | api.vendor.io:8443/v2/... | "api.vendor.io" (Port Stripped!) |
| "https://example.org" (No Trailing Slash!) | example.org + "/" Sentinel | "example.org" (Zero #VALUE! Crash) |
Clean Domain / Hostname Stripping Protocol, www., Port & Path
Removes https:// and http://, slices before the first "/" (using & "/" sentinel) and before ":" (using & ":" port sentinel), and strips leading "www.".
=IF(TRIM(A2) = "", "", SUBSTITUTE(LEFT(LEFT(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A2)), "https://", ""), "http://", ""), FIND("/", SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A2)), "https://", ""), "http://", "") & "/") - 1), FIND(":", LEFT(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A2)), "https://", ""), "http://", ""), FIND("/", SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A2)), "https://", ""), "http://", "") & "/") - 1) & ":") - 1), "www.", "", 1))Detect Secure HTTPS Protocol Compliance
Checks whether the trimmed lowercase URL starts with "https://" for security-link audits.
=IF(TRIM(A2) = "", "Blank URL", IF(LEFT(LOWER(TRIM(A2)), 8) = "https://", "HTTPS Secure", "Insecure / Missing HTTPS"))Try the example
Use the original values or change them to test your understanding.