Ask Your Question

Cannot sort numeric cells correctly

asked 2019-07-07 22:27:14 +0100

TaylorT2 gravatar image

I was given a MS Excel spreadsheet to perform updates. I am using LibreOffice. Two of the columns are numeric. In each cell there is a symbol ' in front of each number. I believe this is preventing me from sorting correctly as I have removed the symbol from some cells and then I can sort correctly. I cannot remove the ' symbol from each cell as the spreadsheet has 10,000 lines. Why is this symbol ' in front of each number in the cells and is there a way for me to remove? Thank you in advance.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-07-08 01:52:52 +0100

robleyd gravatar image

updated 2019-07-08 03:34:55 +0100

The apostrophe tells Calc to treat he contents of the cell as text regardless. It is probably there as a result of how data was imported into the original file. If you press Ctrl+F8 you will see text cells in black, numeric in blue and formulae in green.

To correct the issue, select the column of data, go to Data | Text to Columns and make sure the Field type for the column is Standard then simply click OK. Note you can only process one column at a time.

image description

Edit: to sort numbers followed by letters, try selecting the column(s) to be sorted and use Data | Sort to set the sort keys; then go to the Options tab of the Sort dialog and select Enable natural sort

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. That will help other people with the same question.

edit flag offensive delete link more


Thank you very much. I completed the instructions and it work until.......I think I'm doomed. Column C is sorting properly but the other 2 columns are not as I now see there are a few cells that have 1 letter after the numbers. So now I don't know how to get that to sort correctly. EX.

D-1 100B    3
D-1 100B    4
D-1 101B    4
D-1 102B    3
D-1 102C    3
D-1 102C    4
D-1 10B 1
D-1 10B 2
D-1 118A    1
D-1 119A    2
D-1 119B    1
D-1 11B 1
D-1 11B 2
D-1 120A    1
D-1 120B    2

Any ideas or help would be greatly appreciated. I have searched the internet and not found and answer. Thank you again.

TaylorT2 gravatar imageTaylorT2 ( 2019-07-08 03:06:44 +0100 )edit

It would had been good if you told earlier that your data is not strictly numbers.. and that you want to have sorted 119A between 119 and 120. However, now that you converted the number text to actual numeric content, sorting that mix will always sort numeric before text. You can only convert the numeric content back to text and after that use Enable natural sort. To convert back select the cell range, apply the Text @ number format, and then with Find&Replace (Ctrl+H) use

  • Find:.
  • Replace:$0
  • Other options
    • Current selection only
    • Regular expressions

and hit Replace All.

erAck gravatar imageerAck ( 2019-07-08 18:50:46 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-07 22:27:14 +0100

Seen: 51 times

Last updated: Jul 08