Thread: Ignoring leading characters in a sort

Is it possible to sort a column and ignore leading characters?

I have a list of numbers such as
100
100.1 BOB
100.2
100.3 ABC
100.3 BAC
J 100.1 BOB
JB 100.1 BOB
JB 100.2

I would like to sort them and ignore the J/JB characters that are at the start of some of the numbers. I tried using wildcards in a custom list for sorting (1*, J 1*, JB 1*, 2*..etc etc) but that didn't work. I also don't want to create a custom list as the list has about 20,000 numbers in it as it is (and is constantly growing).

Any help would be much appreciated (even if that help is "no it can't be done").

Is the column format (and each cell in that column) set to 'Text' ?

Did you want a sort that is numerc on the number? - what second key? - what third key?

Did you want 1 to sort before 10 , 100, 2, 20, 200 3, 30, 300 etc?

if you remove the JB from JB 100.2 where would you want that to sort in relatioin to 100.2 (asin line 3 of your example)

Any other pertinent information?

---

Originally Posted by tas666

Is it possible to sort a column and ignore leading characters?

I have a list of numbers such as
100
100.1 BOB
100.2
100.3 ABC
100.3 BAC
J 100.1 BOB
JB 100.1 BOB
JB 100.2

I would like to sort them and ignore the J/JB characters that are at the start of some of the numbers. I tried using wildcards in a custom list for sorting (1*, J 1*, JB 1*, 2*..etc etc) but that didn't work. I also don't want to create a custom list as the list has about 20,000 numbers in it as it is (and is constantly growing).

Any help would be much appreciated (even if that help is "no it can't be done").

I'd like to sort on the numbers and then the trailing characters and NEVER on the leading characters.
ie my sequence would be (in the order I want)
100
100.1
100.1 ABC
J 100.1 ABC
100.1 ABD
J 100.1 BAC
JB 100.2
100.3
101
101.2
JB 101.21
101.22

You can see that JB 100.2 sequences after 100.1 and before 100.3.

Thanks heaps for your quick reply.

The attached shows the steps to extract a sort column from your data, and whilst these can be merged into a more complex single column I have left the working steps for you to see (and amend to suit)

Column D is thus a numeric column that will sort as required, 2nd keys can also be used etc

hth
---

Originally Posted by tas666

I'd like to sort on the numbers and then the trailing characters and NEVER on the leading characters.
ie my sequence would be (in the order I want)
100
100.1
100.1 ABC
J 100.1 ABC
100.1 ABD
J 100.1 BAC
JB 100.2
100.3
101
101.2
JB 101.21
101.22

You can see that JB 100.2 sequences after 100.1 and before 100.3.

Thanks heaps for your quick reply.

Brilliant! I had to edit your formulae a bit to suit but they worked fantastically. Thanks heaps for that.

Good to see, and thanks for the response.

Originally Posted by tas666

Brilliant! I had to edit your formulae a bit to suit but they worked fantastically. Thanks heaps for that.