Sort & lookup with long strings

[ethanstrauss]

Hi,
When performing sorts and lookups (vlookup etc) Excel 2013 seems to only consider the first 255 characters of long strings. The real problem is that it does the sort incorrectly and does not give any warning! It does do equality comparisons correctly.
This is a problem for me because I manipulate DNA & protein sequences in Excel and it is common for the first 255 characters to be the same, but then for sequences to diverge.
I don't have a real problem with Excel not doing this sort correctly, it really isn't designed for this sort of use, but I do have a problem with not giving a warning as it is giving objectively incorrect results!
Ethan

[NBVC]

Perhaps, as a workaround, you can create separate columns that split the strings using LEFT() and MID() functions, then you can sort by first column (left 255 chars) and then second column (remaining chars)?

[ethanstrauss]

Thanks,
That would certainly work. What I actually did was to write my own program to deal with situation. The thing I really take issue with is not that Excel gave me incorrect results in a way that was not at all obvious. At first I came to incorrect conclusions about my data and then it took me several hours to figure out what was going on.
It would be good if Microsoft put in a warning if any of the relevant functions are applied to strings longer than 255.
Ethan