I can't get my Calc-document to sort correct alphabetically

[Geeviz]

Hi people,

I am busy with a list of movies which I put into my Calc-document.
Problem: I can’t get it sorted alphabetically allright.

I have setup all my cells (cell layout) to “Text” and aligned all cells to “Left”.
I mind all spaces at the front so that is not the problem for sorting (a.k.a no spaces at front)

It sorts my movielist now as follows:

10

300

1408

2012

10 Cloverfield Lane

30 Days Of Night

30 Minutes Of Less

300: Rise Of An Empire

etcetera

Calc refuses somehow to sort 300, 1408 and 2012 correctly even when I put those movietitles at the very bottom of column A (1740 rows), or for example put both 300 movies after eachother at the correct place.

Does anyone have suggestions?

Cheers, Geeviz.

[mikekaganski]

Formatting cells does not and must not change the cell’s data type. This is perfectly normal. Cell content data type is decided only when content is entered; and after that, no matter what formatting you apply to the cell, it will be preserved: formatting is about how the data is displayed, not about conversion between numbers and text. The only time the cell format is taken into account in relation with data type is at the moment of data entry, when it helps the program (hints) to decide what data type to expect.

Now you need to convert your existing data. This may be done using re-entry after cells have been formatted as text; or - better IMO - using Data→Text to Columns... feature, where you right-click the column and define Text as its type - then it will convert the whole column at once (which is the function it has been specifically created).

See this screencast.

[Geeviz]

Hmm, I can’t find Data Text to columns…

[mikekaganski]

Menu Data

[Geeviz]

Found it, it works very thank you!

[mahfiaz]

This is a bug. If field format is text, it should be sorted as text value.

As a workaround you could:

1. Start writing the name with ', e.g '10, this makes sure the value is textual.

2. Convert all the values to text with =A1&"", then copy the range and paste back as values (Edit → Paste special → Paste only values icon).

[mikekaganski]

This is a bug. If field format is text, it should be sorted as text value

You are mistaken - please see my answer.

[Geeviz]

From scratch new file (all as text) this is how it still sorted:

1

2

3

3

1a

1d

2d

c

c

c

ff

g

h

s

x

etcetera