Ask Your Question

Sorting column

asked 2017-07-16 12:52:43 +0200

tonyvella gravatar image

I have a column with catalog numbers of stamps. Some of the stamps have a sub category such as 111a, 222b, etc. I formatted the cells of the column as "number" and when I sort I use the "allow natural sort" option. Yet when I sort instead of, say, 111a following 111, I get the 111a at the bottom of the sort order. Can someone please tell me how to format the column and how to sort so that the numerical order is followed properly? Thanks in advance.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-07-16 14:01:41 +0200

Lupp gravatar image

updated 2017-07-16 14:57:24 +0200

(An alternative)

Contents of type 'Number' are always judged "less than" as compared with contents of type 'Text' when sorting. You have to make sure that the unmodified "numbers" actually are also text, technically spoken.

-1- Select the column containing the catalog IDs.
-2- Go to 'Data' > ''Text to Columns'.
-3- Select no separator.
-4- Choose the type 'Text' from the context menu of the column shown there.
-5- OK.

Now the 'Data' > 'Sort' should do as you need it.

edit flag offensive delete link more


Thanks very much. Worked perfectly!

tonyvella gravatar imagetonyvella ( 2017-07-16 14:50:36 +0200 )edit

answered 2017-07-16 13:37:43 +0200

m.a.riosv gravatar image

Format doesn't change the cell's content and doesn't affect how sort it's done.

And 111a it's not a number, so as text it's sorted after numbers not between.

Enable natural sort

Converting all number on the column to text, before sort:

image description

edit flag offensive delete link more


Thanks very much to both. Worked like a charm!

tonyvella gravatar imagetonyvella ( 2017-07-16 14:46:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-16 12:52:43 +0200

Seen: 26 times

Last updated: Jul 16