sort problem

jimmid · Posted: Mon Jun 11, 2012 5:32 pm Post subject: sort problem

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?

ken johnson · Posted: Mon Jun 11, 2012 6:56 pm Post subject:

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).

jimmid · Posted: Mon Jun 11, 2012 8:24 pm Post subject: sort problem

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.

ken johnson · Posted: Mon Jun 11, 2012 9:37 pm Post subject: Re: sort problem

jimmid · Posted: Tue Jun 12, 2012 6:46 pm Post subject: sort problem

Yes the whole column was selected.

ozzie · Posted: Tue Jun 12, 2012 7:50 pm Post subject:

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).