Text & Data Cleaning
Remove Trailing Slash from URLs for Canonical SEO & Lookup Deduplication
Normalize exported sitemap and analytics URLs by trimming spaces and stripping trailing slashes without corrupting bare protocol prefixes or already clean URLs.
Preventing duplicate URL rows in SEO & analytics joins
Search Console, crawler exports, and analytics logs frequently split traffic across "https://example.com/pricing/" and "https://example.com/pricing". Exact XLOOKUP and COUNTIF treat those two strings as completely different keys until trailing slashes are normalized.
Wrapping TRIM(A2) inside LET and testing RIGHT(u, 1) = "/" (alongside LEN(u) > 8 so a bare "https://" stub is never mutilated) strips trailing slashes cleanly while leaving query-string URLs and clean slugs untouched.
| Input URL | RIGHT(TRIM(u), 1) | Normalized Canonical URL |
|---|---|---|
| https://arte-soft.com/pricing/ | "/" | https://arte-soft.com/pricing |
| https://arte-soft.com/pricing | "g" | https://arte-soft.com/pricing |
| https://arte-soft.com/ | "/" | https://arte-soft.com |
| https://arte-soft.com/docs?v=2 | "2" | https://arte-soft.com/docs?v=2 |
Strip Single Trailing Slash After TRIM
Trims accidental spaces first, checks whether the final character is "/" on strings longer than 8 characters, and slices LEN(cleanUrl) - 1 characters from the left.
=LET(cleanUrl, TRIM(A2), IF(AND(LEN(cleanUrl) > 8, RIGHT(cleanUrl, 1) = "/"), LEFT(cleanUrl, LEN(cleanUrl) - 1), cleanUrl))Confirm Canonical Match Between Two URL Variants
Once normalized into column B, COUNTIF(B2:B5, B2) accurately merges traffic rows that previously differed only by a trailing "/".
=COUNTIF(B2:B5, B2)Try the example
Use the original values or change them to test your understanding.