Comparing regEx and Search for sorting bulleted lists in Excel

Sorting chapter/bullets numbers
Quite often you need to sort data that has some kind of chapter numbering, such as
1.1
1.1.2
2.1.1
2.12.2

A specialized form of this would be ipnumbers, for example
192.1.3.2
172.12.180.1

These do not lend themselves to sorting easily, so the obvious solution is to insert leading zeroes. The problem though is how to separate the individual components so as to be able to standardize their widths.

One way is to use the search function in Excel. Here is an example of turning such a form into a set of 4 digit numbers so they can be sorted (1.12.11 into 0001.0012.0011.). Let’s say that the input 1.12.11 is in cell B15, the formula is very complex, and becomes exponentially more complex with more groups of numbers.

=TEXT(IFERROR(MID(B15,1,SEARCH(“.”,B15&”,”)-1),””),REPT(“0″,4)&”.”) & TEXT(IFERROR(MID(B15,SEARCH(“.”,B15 & “.”)+1,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)-SEARCH(“.”,B15 & “.”)-1),””),REPT(“0″,4)&”.”)& TEXT(IFERROR(MID(B15,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)+1,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)+1)-SEARCH(“.”,B15 & “.”)+1)-1),””),REPT(“0″,4)&”.”)

See here for a detailed discussion on how this works.

A better way is to use a regEx approach. Although regEx is available in VBA, it is not directly usable as a spreadsheet function. The Excel RegEx library allows you to use a prebuilt library of regexes or to use your own directly in your spreadsheet. Here is the solution to the same problem using regEx. Let’s say the regEx expression ([0-9]+).([0-9]+).([0-9]+)  is in A15, and the value to be processed is in B15. The solution is

=TEXT(rxreplace(A15,B15,”$1″),REPT(“0″,4)&”.”) & TEXT(rxreplace(A15,B15,”$2″),REPT(“0″,4)&”.”) & TEXT(rxreplace(A15,B15,”$3″),REPT(“0″,4)&”.”)

In addition to being simpler to start with, complexity increase  is linear as more sets of numbers are added.

See http://ramblings.mcpher.com/Home/excelquirks/regular-expressions for more examples of this.

About brucemcp 224 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


*