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

Discuss the spreadsheet application
Post Reply
Kerubu
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Post 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 ?
Last edited by Kerubu on Sat Jun 05, 2010 10:17 pm, edited 1 time in total.
OpenOffice 3.2 ubuntu 10.04
User avatar
RoryOF
Moderator
Posts: 34571
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Post by RoryOF »

Can you define your sort rules explicitly, rather than just giving an example?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

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

Post 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.
Apache OpenOffice 4.1.1
Windows XP
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Kerubu
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Post 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 ! :?
OpenOffice 3.2 ubuntu 10.04
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Post 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.
AOO4/LO5 • Linux • Fedora 23
Kerubu
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Post 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.
OpenOffice 3.2 ubuntu 10.04
User avatar
acknak
Moderator
Posts: 22756
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

Post 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
Attachments
text+num_sort.ods
(7.91 KiB) Downloaded 398 times
AOO4/LO5 • Linux • Fedora 23
Kerubu
Posts: 17
Joined: Tue May 25, 2010 3:34 pm

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

Post 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 ?
OpenOffice 3.2 ubuntu 10.04
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Post 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. ;-)
AOO4/LO5 • Linux • Fedora 23
Post Reply