[Solved] Sort Data Numerically

[hroberts89436]

Ok I searched thru the forum and I am having an issue with sorting numbers. Here is a small sample that I have sorted as numbers->general:

10000075
1000011
10000687
100055
1000627
10006530
10007358
10011250
1001475
100155
1001648
10022936
10023065
1002750


Reminder this is after the sort of over 9,000 numbers why wont it sort properly, how can I make it sort numerically?

[gerard24]

Your datas are not numbers, so you sort them alphabetically.

[Tutorial] Ten concepts that every Calc user should know

[RoryOF]

Turn on / View / Value Highlighting. All numeric entries are now highlighted in blue, all alphabetic entries in black.

[Villeroy]

Reminder this is after the sort of over 9,000 numbers why wont it sort properly

How do 9000 numeric text values get into your spreadsheet? This does not happen by accident.

[hroberts89436]

Your datas are not numbers, so you sort them alphabetically.

[Tutorial] Ten concepts that every Calc user should know

Read my post. I already stated they were numbers. I highlighted the entire column, selected format cells, then numbers, general.

[hroberts89436]

Reminder this is after the sort of over 9,000 numbers why wont it sort properly

How do 9000 numeric text values get into your spreadsheet? This does not happen by accident.

this is a dump of a database. these are sales ranks I need to sort to know which is the better selling items.

[RoryOF]

You data are not sorting as numbers. Try / View / Value Highlighting, which is the proof of whether they are or are not numbers. If they are not numbers, then you need to find a mechanism to turn them into numbers.

[acknak]

... I highlighted the entire column, selected format cells, then numbers, general.

It is a widely-held misconception that doing this has some effect on the values stored in the cells. It does nothing.

If you had text values in the cells to begin with then you still have text values, regardless of what number format is applied to the cells.

[thomasjk]

If I cut and paste your data into Calc they paste as values. But as has been said previously you likely have text not values. Try using the Value function to convert the text to data that can be sorted.
Value(Text) is the formula.

[Villeroy]

Reminder this is after the sort of over 9,000 numbers why wont it sort properly

How do 9000 numeric text values get into your spreadsheet? This does not happen by accident.

this is a dump of a database. these are sales ranks I need to sort to know which is the better selling items.

Does that sequence of numbers ...

Code: Select all

10000075
1000011
10000687
100055
...

represent rank 75, 11, 678 and 55? Then you need to cut off the leading 1 before converting the text to number.
I think that these are ID numbers of some items, similar to part numbers, zip codes and phone numbers.
Purposely, the database keeps these ID numbers as text and any decent application will import them as text. The text "123" is just a sequence of 3 characters that happen to be digits. Just like the sequence "ABC" the sequence "123" has no numeric value at all.
"0123" is another sequence than "123". Contrary to the text "0123", the number 0123 (formatted with leading zero) is exactly the same numeric value as number 123.

Still you did not anwer how the values get into the spreadsheet.
You can tell the database to export integer numbers (... CAST("ID", INT) AS "IDNUM" ...).
If the database dump is a text file you can import the column as numbers.

[hroberts89436]

Still you did not answer how the values get into the spreadsheet.

OK so I turned on highlighting. Yes you are correct even though I keep telling the system they are numbers they are staying text. how can I force them to be numbers? These are numbers that go from 1 to 9,999,999, and 0(zero).

I download an xls spreadsheet from my database on a server that has columns to CW and rows to 9,136 (and grows everyday). I want to sort them by sales rank so I can remove items above a specific rank from my database (this means they aren't selling)

[hroberts89436]

I just noticed that in the input line there is an apostrophe before the number, how can I easily remove that? I remove it manually and it converts to a number, but to do this to over 9000 rows a couple of times a week would not work.

OK here is a strange thing, I just opened it in Office 2007, and it opened properly as numbers, so how can I get open office to accept it?

[Villeroy]

The apostrophe prevent the interpretation of the string as number because the source data were text. If you would tell us how the data get into the sheet, we could tell you how to import the numeric text as values.
The conversion of wrong data goes like this:
1. Select the column.
2. Format>Cells... set any number format you'd like to see but not "@" of category Text
3. Edit>Find>Replace
[More Options]
[X] Current selection only
[X] Regular expressions
Search .+ (dot and plus)
Replace &
[Replace All]
Basically this retypes all values.

[hroberts89436]

I don't know how the data gets into the spreadsheet, I ask the system to download my inventory, I then get an email telling me the file is ready to be picked up, and then I click on the link and download the file.

Ok I tried your solution, cutting and pasting from your post into find and replace the items, I get the message: Search key not found, Im assuming that the symbol in replace is the ampersand, that is what it converted to.

Ok I don't know if it uploaded, I tried to upload the file

[hroberts89436]

OK well I solved it, I used Crappy ass microsoft excel, selected the column, right clicked and selected convert to number. I then saved the file and opened it in open office and it now shows as a number, and sorts correctly. Does Libre Office or Open Office have this functionality? Why can't it be this simple in these apps? I would rather not have to ever touch Office, but it seems that Open Office and Libre Office are so far behind in some things that I will have to keep Microsoft products around.

[hroberts89436]

OK here is a link to the file: http://db.tt/j76rdksm

Edit: 2024-02-20 Broken link -- MrProgrammer, forum moderator

If someone can tell me how to convert these columns to numbers without using the stupid microsoft products I'd appreciate it. I'm mostly interested in Column A and CE

[gerard24]

OK well I solved it, I used Crappy ass microsoft excel, selected the column, right clicked and selected convert to number.
...
Does Libre Office or Open Office have this functionality?

• Select the column
• menu Data > Text to column
• changes nothing in the dialog box, press OK

[hroberts89436]

Gerard..... You are a genius. That worked perfectly. So simple and yet so working.

Thank you very much

[Bernadine]

I was on the verge of tears, why wouldn't Open Office sort a simple list of numbers 1 to 30? No matter what I tried all I got was 1's then 2's. This solved it so easy. Going to save your answer in several places so I never lose how to do this.
THANK YOU,
Bernadine

[Zizi64]

OpenOffice 3.4.1 on Windows 8

Please update your signature (and/or upgrade your office suite). The (A)OO3.4.1 is an achaic old version.
It is better to install LibreOffice today.

[vbunch]

I was on the verge of tears, why wouldn't Open Office sort a simple list of numbers 1 to 30? No matter what I tried all I got was 1's then 2's. This solved it so easy. Going to save your answer in several places so I never lose how to do this.
THANK YOU,
Bernadine

yeah, I was crying today about stupid sort. but whaht exactly do you mean "this solved it"? what is "this".

[FJCC]

I suppose "this" is the following:

Select the column
menu Data > Text to column
changes nothing in the dialog box, press OK

[Bernadine]

Yes, this is this:
"Select the column
menu Data > Text to column
changes nothing in the dialog box, press OK"
I thought I was replying to the post with those instructions in it, but these posts seem to get all crunched up and maybe I didn't. I don't know, very confused except by quoted answer.