Very often I am required to find duplicates in whole rows of data. To do this I concatenate all the columns of a table together and and search for duplicates over the concatenated data. From here I can either extract the duplicates, remove them, or just "disable/close" them.

See this image:
http://imgur.com/gallery/eFx49uc/new

Red arrow - These are the formulas that create the numbers in cells A1 to A4

Cells A1 to A4 have conditional formatting "highlight duplicates" enabled. As you can see, strings longer than 256 characters are treated as unique values even though they are clearly not.

Also note Match() [dark blue] and CountIf() [purple] functions do not find matches above 256 characters.

The strange thing is that this isn't a problem where Excel can't calculate them as the same value. This is shown by the light blue arrow. =A3=A4 still provides true.

Similarly in VBA you can do:
?range("A3").value = range("A4").value
This will also return TRUE.

This shows that it is a bug with the WorksheetFunctions and not with Excel/VBA generally.

Also noteworthy is that you can create VBA algorithms to search for duplicates and these work fine, although often they are slow. Furthermore Excel's "remove duplicates" feature doesn't have a character limit, so from that we can already know that it is by no means impossible for MSOffice to implement these extended character limits.

P.S. There are also numerous other posts about character limits in Excel. See:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10208379-remove-the-255-character-limit-in-excel-object-mod
&
https://social.msdn.microsoft.com/Forums/office/en-US/54d259bd-a2ae-4118-87b0-a59035e6ef75/please-remove-remaining-255-character-api-limitations?forum=exceldev

I think all these limitations should be a priority especially since businesses often rely on this feature to identify duplicates in large datasets..