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.

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *