Sort order on very large spreadsheet.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several cells that will not sort properly. Is there some kind of
setting that will prevent them from sorting from a to z.
When I manually enter some of the problem cells again in another workbook,
they sort fine.
 
There may be leading spaces or non shown characters
try in a helper column
=trim(c1)
or clean(c1)
then try to sort on this column
 
Thanks, where do I enter this?

bj said:
There may be leading spaces or non shown characters
try in a helper column
=trim(c1)
or clean(c1)
then try to sort on this column
Click to expand...
 
I would put it in a cell next to the top of the data and then copy down
for example if your data is in Cells C1 to C2000
I would insert a new column in column D and in D1 enter
=trim(clean(C1))
and copy D1 and paste to end of data in column D.
 
I have found that "Sort" does not work properly when there are "nonbreaking"
spaces (you cannot see them in Excel) in your spreadsheet. To get convert
them to regular spaces as follows:

CTRL+H {shortcut for EDIT REPLACE}
Find what: {enter "Alt+0160" --without the quotes, using NUMPAD numbers}
Replace with: {enter one regular space here}
Click on REPLACE ALL
Do you want to replace the rest of the document? NO
OK
 
Back
Top