Ubuntu
openoffice.org package

Problem sorting a column of numbers

Asked by John Spurrier

when sorting on a column with numbers 1 thru 15 in random order, then doing a sort on that column in ascending order I get 1 followed by 10, 11, 12, 13, 14, 15, 2, 3,..... Help!

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
Last reply:
Revision history for this message
Allan Shand (ashand79) said : 		#1

I would check if the numbers are formated as numbers or text I had a similar problem and I am sure that was what the problem was.

Hope this helps.

Revision history for this message
Aruna Hewapathirane (aruna-hewapathirane) said : 		#2

Select your column with the data, right clcik and select format cells then select Number format for the range you want to sort.

I believe you have your data formatted as text and this will sort exactly the way you have explained as your data will be treated a stext and not numbers.

Revision history for this message
Tony Pursell (ajpursell) said : 		#3

Hi John

You don't say if you are sorting data in a Calc spreadsheet or in a table in a Writer text document. If you are sorting data in a Calc spreadsheet, follow on of the suggestions above. For a table in a Writer text document, you will find you have the option of an Alphanumeric sort (which gives the order you don't want) or a Numeric sort which is want you want,

Tony

PS If we have answered your question, please mark it as Solved.

Revision history for this message
John Spurrier (jspurrier1) said : 		#4

Hi All,

Revision history for this message
John Spurrier (jspurrier1) said : 		#5

Hi All,
I tried your suggestions to be sure the numbers in the column were formatted as numbers but the problem persists.
Help?

Revision history for this message
Best Tony Pursell (ajpursell) said : 		#6

Hi John

I've found the problem. Format > Cells just changes the way the numbers are displayed. It does not change the nature of the cell contents. I.e they are still text displayed in a numeric format, so they sort as text. You need to do a Text to Number conversion as follows

1.Select the column in which the digits are found in text format. Set the cell format in that column as "Number".

2.Choose Edit > Find & Replace

3.In the Search for box, enter ^[0-9]

4.In the Replace With box, enter &

5. Click on the 'More' button

6.Mark the 'Regular expressions' check box

7.Mark the 'Current selection only' check box

8.Click on Replace All

You can then do your Sort.

Tony

PS If this answers your question, please mark it as Solved.

Revision history for this message
John Spurrier (jspurrier1) said : 		#7

Thanks Tony Pursell, that solved my question.

Revision history for this message
Nic Knox (baknox) said : 		#8

I tried Tony's scheme but it did not work:
In 'Search for' I input "^(0-9)" also tried "^[0-9]"
In 'Replace with' I input "&"
Dialogue reported that "Search Key Not Found".

However I started a parallel column [B] containing the formula "=A1*1" etc [where Col A contained the text numbers]: it sorted properly. BUT tried it on a different sheet and col B just returned '#VALUE!'.
So, not too helpful!

Revision history for this message
Tony Pursell (ajpursell) said : 		#9

Possibly your problem is different.

Anyway, you should not be highjacking a Solved question like this.

Please ask a new question and give more details of how your data is formatted.

Tony