Ask Your Question
2

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

asked 2018-09-16 20:25:54 +0200

Geeviz gravatar image

updated 2018-09-16 20:27:32 +0200

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2018-09-17 07:24:40 +0200

Mike Kaganski gravatar image

updated 2018-09-17 16:38:17 +0200

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

edit flag offensive delete link more

Comments

Hmm, I can't find Data Text to columns...

Geeviz gravatar imageGeeviz ( 2018-09-17 16:28:10 +0200 )edit

Menu Data

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-17 16:29:53 +0200 )edit

Found it, it works ;) very thank you! :)

Geeviz gravatar imageGeeviz ( 2018-09-17 16:37:33 +0200 )edit
0

answered 2018-09-17 16:32:09 +0200

Geeviz gravatar image

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

edit flag offensive delete link more
0

answered 2018-09-17 07:09:44 +0200

mahfiaz gravatar image

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<enter>, 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).

edit flag offensive delete link more

Comments

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

You are mistaken - please see my answer.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-17 07:25:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-16 20:25:54 +0200

Seen: 33 times

Last updated: Sep 17