Sort order on very large spreadsheet.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dennis1188 - 06 Mar 2007 15:51 GMT

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.

Reply to this Message

bj - 06 Mar 2007 15:59 GMT

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

> 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.

Reply to this Message

Dennis1188 - 06 Mar 2007 16:42 GMT

Thanks, where do I enter this?

> There may be leading spaces or non shown characters
> try in a helper column
[quoted text clipped - 6 lines]
> > When I manually enter some of the problem cells again in another workbook,
> > they sort fine.

Reply to this Message

bj - 06 Mar 2007 17:07 GMT

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.

> Thanks, where do I enter this?
>
[quoted text clipped - 8 lines]
> > > When I manually enter some of the problem cells again in another workbook,
> > > they sort fine.

Reply to this Message

PlayingToAudienceOfOne - 31 May 2007 17:32 GMT

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

Reply to this Message