I’m trying to find duplicate web addresses in my Google Sheets column using this formula:
=if(counta(unique(B:B))<>counta(B:B), "Duplicates found", "All unique")
The formula works perfectly for regular text entries, but it has weird behavior with URLs that I copy and paste. When I paste the same link twice in column B, the formula doesn’t recognize them as duplicates. It only detects a duplicate when I have three identical links in the column.
For example, if I copy and paste “https://example.com/documents/manual.pdf” into two different cells, my formula shows “All unique” instead of “Duplicates found”. But when I add a third copy of the same URL, then it finally says “Duplicates found”.
This seems like a bug with how the unique() function handles pasted links. Has anyone encountered this issue before? I need a reliable way to check for duplicate URLs in my spreadsheet columns.
Google Sheets treats pasted URLs as rich text, not plain strings. I ran into this exact issue managing link inventories for clients. The unique() function sees different hyperlink formatting even when URLs look identical. Here’s what fixed it for me: convert URLs to plain text first. Try wrapping your range with VALUE() or TEXT() functions before checking duplicates. Or paste using “Paste special > Values only” to strip hyperlink formatting. I’ve noticed this varies by source too - browser links carry more formatting baggage than URLs typed directly into cells. That three-duplicate threshold you’re seeing? There’s probably formatting differences between your paste operations that’s throwing off unique().
This happens because Google Sheets treats hyperlinks differently than plain text. When you paste URLs, Sheets creates hyperlink objects instead of simple text, which messes with unique()'s comparison logic. I’ve run into this with campaign URL tracking. ARRAYFORMULA with REGEXEXTRACT works better - it normalizes everything first. Try: =if(counta(unique(ARRAYFORMULA(REGEXEXTRACT(B:B,“.*”))))<>counta(filter(B:B,B:B<>“”)), “Duplicates found”, “All unique”). REGEXEXTRACT strips the formatting and converts everything to plain strings before checking for duplicates. The filter part skips empty cells that can throw off the count. Works way more reliably than other text cleaning functions for pasted web addresses.
Been there, done that. Google Sheets creates hyperlink objects when you paste URLs, not plain text. Your unique() function can’t compare these objects properly.
Formula fixes work sometimes, but they break with mixed data types or special characters in URLs. Plus debugging spreadsheet formulas sucks when you’re dealing with hundreds of links.
I automate this now. Set up a workflow in Latenode that connects to your Google Sheet, pulls all URLs as plain strings, runs duplicate detection with proper string matching, then writes results back.
The workflow triggers when you update the sheet, so you get real-time duplicate checking without formula headaches. You can also add validation like checking if URLs are still active or flagging suspicious domains.
Works way better than fighting spreadsheet quirks, especially with large datasets.
I’ve hit this same issue with Google Sheets and URLs. The problem is Google Sheets adds invisible formatting when you copy/paste URLs, so identical URLs look different to the unique() function.
You could try wrapping your URLs with TRIM() and CLEAN() functions, but spreadsheet formulas get messy fast for data validation at scale.
I’d automate this instead. I built a workflow in Latenode that watches my Google Sheets, grabs URLs from specific columns, and runs proper duplicate detection with actual string comparison. No weird spreadsheet bugs.
It runs every time I update the sheet and flags duplicates in a separate column. Way more reliable than debugging Google Sheets formulas, plus I can add other checks like testing if links still work.
Latenode has native Google Sheets integration, so setup’s pretty easy. Worth trying if you do this kind of data validation regularly.
Same thing happened to me! Hidden characters get copied with URLs - that’s your problem. Use =TRIM(CLEAN(B:B)) instead of just B:B in your formula. Also check for trailing spaces since browsers sometimes add them when you copy. Fixed it for me with product links.