OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

sort problem

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jimmid
Newbie
Newbie


Joined: 11 Jun 2012
Posts: 3
Location: Adelaide,Australia

PostPosted: Mon Jun 11, 2012 5:32 pm    Post subject: sort problem Reply with quote

Hi, I have tried to sort a spreadsheet by numerical values in a column from highest to lowest, but when I sort it sorts wrong. Like for highest to lowest it will put 99 at top, then 98, 89, 857, 6599, 55, 4998, etc. It appears to look at the numbers left to right instead of the whole number, there for saying 99 is bigger than 3,984. How can I fix this?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Jun 11, 2012 6:56 pm    Post subject: Reply with quote

They must be text, not numbers.
Change the format to Number General (Default format, Ctrl+M) then use Find & Replace with regular expressions to convert them to numbers then redo the sort.
On the Find & Replace dialogue use...
Search for...
.+
Replace with...
&
Current Selection
Regular expressions

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jimmid
Newbie
Newbie


Joined: 11 Jun 2012
Posts: 3
Location: Adelaide,Australia

PostPosted: Mon Jun 11, 2012 8:24 pm    Post subject: sort problem Reply with quote

I checked that the column is formated as text and it says it was number - general. Tried ctrl M and still said same format, but when I tried find & replace it said expression not found. Each column has a header so ticked that in sort options.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Jun 11, 2012 9:37 pm    Post subject: Re: sort problem Reply with quote

jimmid wrote:
when I tried find & replace it said expression not found.
Were the offending cells selected?

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jimmid
Newbie
Newbie


Joined: 11 Jun 2012
Posts: 3
Location: Adelaide,Australia

PostPosted: Tue Jun 12, 2012 6:46 pm    Post subject: sort problem Reply with quote

Yes the whole column was selected.
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Tue Jun 12, 2012 7:50 pm    Post subject: Reply with quote

Hi jimmid
I think perhaps you have used just the + in the 'search for' option
It is should be '.+' (ie period plus)
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group