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

Discuss the spreadsheet application

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

Postby Kerubu » Sat Jun 05, 2010 5:48 pm

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 ?
Last edited by Kerubu on Sat Jun 05, 2010 10:17 pm, edited 1 time in total.
OpenOffice 3.2 ubuntu 10.04
Kerubu
 
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Postby RoryOF » Sat Jun 05, 2010 6:04 pm

Can you define your sort rules explicitly, rather than just giving an example?
Apache OpenOffice 4.0.1 on Xubuntu 13.10 and Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 14346
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Charlie Young » Sat Jun 05, 2010 6:07 pm

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.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1435
Joined: Fri May 14, 2010 1:07 am

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

Postby FJCC » Sat Jun 05, 2010 6:18 pm

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.
AOO 3.4 or 4.0 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 3672
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Kerubu » Sat Jun 05, 2010 6:22 pm

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 ! :?
OpenOffice 3.2 ubuntu 10.04
Kerubu
 
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Postby acknak » Sat Jun 05, 2010 7:07 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Postby Kerubu » Sat Jun 05, 2010 10:16 pm

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.
OpenOffice 3.2 ubuntu 10.04
Kerubu
 
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Postby acknak » Sat Jun 05, 2010 10:48 pm

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
Attachments
text+num_sort.ods
(7.91 KiB) Downloaded 148 times
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Postby Kerubu » Sat Jun 05, 2010 10:56 pm

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 ?
OpenOffice 3.2 ubuntu 10.04
Kerubu
 
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Postby acknak » Sat Jun 05, 2010 11:59 pm

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. ;-)
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17399
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Postby MrProgrammer » Sun Jun 06, 2010 3:37 am

Kerubu wrote:I want to order by the number part first


That statement isn't clear to me. Let's consider "9AB" and "12CD". If we "order by the number part", is 9 before 12 (because 9 is less than 12) or is 12 before 9 (because the character "1" sorts before "9")? None of the examples provided distinguish between these situations.

If numbers are to be ordered by their characters (12 before 9), then what has been discussed so far will work. Otherwise we need to right justify the numeric characters before sorting them. It's easiest if you know, and I presume you do, the maximum number of digits in a number. I'm going to assume this is eight. If numbers are to sort numerically, then we can use as sort keys "00000009AB" and "00000012CD", and 9AB will sort before 12CD. The attached example shows the functions needed to generate these keys.

201006051928.ods
(8.25 KiB) Downloaded 134 times
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1740
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests