Calc - How do you sort a list of numbers alphabetically?

[codywohlers]

I have a list of serial numbers of different lengths that I am trying to sort alphabetically but I can’t find an option. Sorting by “Ascending” just does it numerically and puts, for example, 6 before 110. I tried changing the cell types to Text but it still wouldn’t sort.

110                           6
6   --- sort "Ascending" ---> 42
42                            110

How can I sort it alphabetically?

110                           110
6   ---- sort this way??? --> 42
42                            6

[anon73440385]

I tried changing the cell types to Text

This idea is correct, but you need to format your cells to Text before you enter your “numbers”. Once a cell contains a specific type you can’t convert the type (e.g. number → text) by just formatting. You could turn your numbers into text using Edit ->Find and Replace, Find: (\d+); Replace: '$1 and Other Options: [x] Regular expressions.

[codywohlers]

Turns out when the cell type was changed to Text the data is still considered a number until each cell is edited. I had to go through the list and manually edit each cell (just added a space after and then deleted the space).

Cutting and re-pasting the entire column still did not change the data to text. I wonder if there is a another question or bug report related to this?

[erAck]

Applying a number format never changes content in the cell. Cut&Paste just pastes the original content with original number formats, so no change either, and not a bug.

The comment by Opaque at the question to use a Find&Replace regular expression for mass replacement quite nails it.

[Photon713]

I seem to recall when I had to sort, I had to pad the numbers with zeroes, normally ascending for ranking. I did the sort and either ignored the leading zeroes or removed them after sorting. If you’re doing a large table you can append the zeroes with a subroutine and strip them off later with another subroutine. If your column is part of a table, then select the entire table, but, sort by the column of interest (with sorted numbers in this case). Someone else might have a better suggestion, I’m trying to recall work done 10 years ago.

[LeroyG]

To sort the numbers alphabetically:

• Select all numbers, choose menu Data - Text to Columns…, select the column header and choose Text in Column type: - OK.
• Choose menu Data - Sort… - …

If need to revert the cell format as Number Standard:

• Select all numbers, choose menu Format - Cells… - Numbers tab - Category Number - Format Standard - OK.
• Select all numbers,¹ choose menu Data - Text to Columns…, select the column header (in Column type: Standard is selected by default) and OK.

¹ Here may be a bug: sometimes if selecting from the last to the first cell, only last cell showed in Text to Columns…. Now can’t repeat it. With LibreOffice 6.3.6.2 (x86); OS: Windows 6.1.

---

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark () to the left of the answer that solves your question.

[Lupp]

Assuming the numbers that should be converted to (and then treated as) text are in one column, the menu item >Data>Text to Columns... can do it despite its (insofar) misleading name:
Select the column (the range within).
Call the mentionedf menu item.
Rightclick the header of the shown table coulmn.
Select Text.
OK.

This can, of course, not restore already lost leading zeroes.