When working with URLs in Google Sheets, sometimes you want to compare just the URI (the part after the domain) and ignore the domain and protocol (like http, https, or www). Here's a handy formula that does exactly that—extracting the URI and checking if it's the same across different columns.
Code:
=IF(AND(MID(A2, FIND("/", A2, 9), LEN(A2)) = MID(E2, FIND("/", E2, 9), LEN(E2)), MID(A2, FIND("/", A2, 9), LEN(A2)) = MID(H2, FIND("/", H2, 9), LEN(H2)), MID(A2, FIND("/", A2, 9), LEN(A2)) = MID(K2, FIND("/", K2, 9), LEN(K2))), "Match", "No Match")How It Works:
- FIND("/", A2, 9) locates the first / after the domain name, assuming the domain starts after the first 9 characters. This works for both http:// and https://.
- MID(A2, FIND("/", A2, 9), LEN(A2)) extracts the URI (everything after the domain) from the URL.
- The AND function compares the URIs from columns A, E, H, and K.
- If all the URIs match, it returns "Match". Otherwise, it returns "No Match".