I have a workbook that I am pasting some very long SQL script strings into column A, as in the length of each individual line that is pasted into each cell can sometimes be nearly 5000 characters.
There are about 150,000 lines in the SQL script, but this doesn't seem to be the issue.
I want to compare these SQL script commands to the commands generated by an old system. Unfortunately the old and new systems don't always generate the commands in the same order, so I want to sort column A so they will both line up and I can compare them.
Unfortunately the sorting doesn't seem to be working. It does sort things, but it only seems to use some of the string, and differences that are near the end of the string don't seem to be considered.
For example if there were some strings like below where . indicates a new cell and the 1000 characters are identical between rows:
.INSERT INTO MY_TABLE SELECT x.ID, 10, "Customer", NULL ... <1000 characters> , WHERE X.ID = "cde"
.INSERT INTO MY_TABLE SELECT x.ID, 10, "Customer", NULL ... <1000 characters> , WHERE X.ID = "abc"
.INSERT INTO A_TABLE SELECT 10, 20, "TEST"
Then sorting the column would result in:
.INSERT INTO A_TABLE SELECT 10, 20, "TEST"
.INSERT INTO MY_TABLE SELECT x.ID, 10, "Customer", NULL ... <1000 characters> , WHERE X.ID = "cde"
.INSERT INTO MY_TABLE SELECT x.ID, 10, "Customer", NULL ... <1000 characters> , WHERE X.ID = "abc"
So the A_TABLE being less than MY_TABLE sorting worked, but the MY_TABLE sorting did nothing.
I seem to run into the issue once lines get about around 600+ characters (but that is simply the size of my string when things go wrong so is an approximate guess)
Does Excel have a limit to the size of cells it can sort?
Is there a way around this? Or some other way to do it?
All help appreciated
The reason is the limitation of Excel formula string length cannot be more than 255 character. Sadly this is applied for simple string in a cell if you do some sort function on it. The workaround is to concatenate the string of parts shorter than 255 characters each.
In cell: =text1&text2&text3[&text4...]
It will work then up to other limit of Excel (~32760)
SO link on this issue: