Page 1 of 1

[Solved] How to sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 5:48 pm
by Kerubu
Hi,

I'm trying to sort some data into a specific order. For example I have the following

21
21A
21AB
22
25
25ZC
45
45A

Now if I do a standard sort I end up with :
21
22
25
45
21A
21AB
25ZC
45A


But what I actually want is this :

21
21A
21AB
22
25
25ZC
45
45A

Is there anyway I can sort it this way ? It's got me a bit stumped. Regex perhaps ?

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 6:04 pm
by RoryOF
Can you define your sort rules explicitly, rather than just giving an example?

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 6:07 pm
by Charlie Young
The cells containing numbers only are being interpreted as numbers, and the cells which include letters are being interpreted as text. The sort puts the pure numbers first. You can either format the cells as text before entering the data, or type a ' in front of the numbers. There are other possibilities.

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 6:18 pm
by FJCC
You want the cells sorted as if they contain text, so you just have to enter the contents that way. If it is just a short list, set the cells to have the text format by going to Format -> Cells and in the Category window select Text. Then just type in the list. If the list already exists and is long, select all the cells and set the format to Text as above. Then go to Edit -> Find & Replace. In the Search For box enter .* (period asterisk) and in Replace With enter &. Select More Options and enable regular expressions and Current Selection Only. Click on Replace All and all the cells will be re-entered as text. You should be able to sort as you want.

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 6:22 pm
by Kerubu
RoryOF wrote:Can you define your sort rules explicitly, rather than just giving an example?
OK each data item will be of either a number, or a number with characters at the end (ALWAYS at the end) e.g.

12 (read as twelve)
12AB (read as twelve A, B)
223XD
223XE

I want to order by the number part first (which is ALWAYS present) and then sort by the Character part (which is NOT always present). The list above is how I would want it presented and not 12AB at the bottom which is what Calc does during a sort.

I can't really put it any other way than examples really ! :?

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 7:07 pm
by acknak
You can also just add a new column, with a formula like =LEFT(A1;99) that will render the data cells all as text, and then sort by that new column.

Re: How can I sort Alpha-Numeric data to MY sort rules ?

Posted: Sat Jun 05, 2010 10:16 pm
by Kerubu
Thanks to all. However I couldn't get any of the methods to work (probably me !) but I came up with my own long winded method in the end.

Basically I copied the column of data into two columns.

On the first column I did a search and replace using a regex : search for characters and replaced them with "";
On the second column I searched for numbers and replaced them with "".

that gave me two columns, numerical and characters.

I then had to fiddle about making sure the numerical column was indeed numerical (OO decided half of the numbers should be text !)

I then sorted by the first column, then by the second. That gave me the data as I wanted it, but in two columns. I just concatenated the two columns then to get a final result.

Re: [SOLVED]How can I sort Alpha-Numeric data to MY sort rul

Posted: Sat Jun 05, 2010 10:48 pm
by acknak
I'm not sure where you might have had trouble, but here's a sample you can look at. You really ended up taking the long way around.
You can also just add a new column, with a formula like =LEFT(A1;99)...
Even simpler: =""&A1

Re: [SOLVED]How can I sort Alpha-Numeric data to MY sort rul

Posted: Sat Jun 05, 2010 10:56 pm
by Kerubu
Ooo thanks for that, that is nice and easy

I'm not an expert at spread sheets and Calc wouldn't change half the data to numerical for some strange reason... so I just did what worked with it. I'm curious as to what the command =""&A1 is instructing calc to do ... i.e. what does the "" ,& mean individually ?

Re: [Solved] How to sort Alpha-Numeric data to MY sort rules

Posted: Sat Jun 05, 2010 11:59 pm
by acknak
The ampersand, &, is the concatenate operator. It takes two text values and concatenates them into one: "A" & "B" --> "AB". Since & works only on text values, Calc converts any number to text before passing it to &. For example: "A" & 123 --> "A123". Here, we only need the conversion, so we concatenate the number with an empty text value: "" & 123 --> "123".

You get the same result if you use =CONCATENATE("";123), or even just =CONCATENATE(123), my suggestion just saves a little typing. ;-)