Alphanumeric sorting (4 elements) of 77,000+ lines! SQL option too.

Hi All!

I am trying to sort 77,000+ lines of alphanumeric data. Ideally I don't want to split text to columns and filter on separate pieces as there is far too much data and the string is of varying lengths (I've so far only done this on the letter and still a large portion is out of sequence).

There are multiple variations of the alphanumeric data and i would first like it to order by the middle letter 'a','b','c','dthen by the first string of numbers, then by the other string of number, and then finally the numbers on the end.

[second element (number string with/without colon ( separating the numbers - it is still one string of varying length)].[first element (one character letter).[third element (number of character length 2-3)].[fourth element (number of characters 1-4)]

The primary issue is there is a lot of decimalisation, varying punctuation, no fixed lengths which makes this a nightmare!

I can also access the raw data in SQL so i have the ability to Regex there too! e.g. evaluate('REGEXP_INSTR(%1, ''^[0-9].*A+$'')',"TABLE"."COLUMN")


example of the order i would like :
18.a.89.1
18:27.a.90.1
18:3.a.90.1
18:3.a.90.1a
18:3.a.90.11a
18:3.a.90.22
18:3.a.90.22a
18:3.a.90.23
67:7.a.90.5
69:01.a.1.1-
70:1.a.85.1
70:1.a.85.1-4
70:1.a.85.12-
100:01.b.7.53
10:1.b.90.1
10:1.b.95.1
10:1.b.95.2
10:1.b.95.3
10:2.b.95.1
10:2.b.95.2
58:5.b.201.1
58:5.b.95.1-2
58:5.b.95.6
59:1.b.200.1
69:01.b.36.55
2:55.c.95.422
2:55.c.95.423
2:55.c.95.424
44:5.c.201.16
44:5.c.201.5
44:5.c.90.22
60:1.c.200.14
60:1.c.200.2
60:1.c.202.2
1:8.d.95.2
1:8.d.95.3
1:8.d.95.4
61:29.d.90.1

Please Login or Register  to view this content.

There are multiple variations of the alphanumeric data and i would first like it to order by the middle letter 'a','b','c','dthen by the first string of numbers, then by the other string of number, and then finally the numbers on the end.

But the example is not so .
c(Row 6) is coming after a(Row 5), then once again a(row 7) is coming

Try In R4

Formula:

Please Login or Register  to view this content.

@KV

There are functions in that formula that don't exist in Excel 2021 (e.g TEXTBEFORE). You yourself have 2019 in your profile - if you're using 365, then it needs updating.

@potofteatohavein

The formula in post #5 will not work in Office 2021.

@kv: Excel 2021 doesn't have the TEXTxxxxxx functions or HSTACK or SORTBY. I don't think 2019 does either.

Actually, 2021 DOES have TEXTJOIN, which first appeared in 2019, but it does not have the other three. It certainly doesn't have HSTACK (365), but it does have SORTBY (2021).

For anyone who's still getting caught out, bookmark this site: https://bettersolutions.com/excel/fu...-functions.htm

So, if it's in 2019, it's also in 2021?

Yes - correct.

@AliGW
I have already told that I am having 2019. With the help of internet I am using 365 functions.

Regarding other functions that are not in 2021 I am not aware of it.

I have shared this link above: https://bettersolutions.com/excel/fu...-functions.htm

Bookmark it and refer to it in future, please.

There is no point in offering a 365 solution to someone with Excel 2021 - it simply won't work.

Thanks for the suggestion and link. If somebody having same problem and having version 365 it may help him in future.
Here is the VBA solution for 2021 and other versions.
Assuming BA:BE columns are free, they are used as helper columns during running macro. If they are not free columns are to be changed.
Result is in Column R.

Please Login or Register  to view this content.