Friday, September 21, 2012

Sorting Outline Numbers in SharePoint list using Calculated columns

Requirement: We have a column in a list named SNumber. The value for this is would be similar to outline numbers in Word documents.

1.1
1.2
1.3
1.10
1.1.1
1.1.2
1.2.3.3
etc

Problem: We were not able to sort the values, and the values are sorted in the following order. The SharePoint calculated field formulas are not strong enough to achieve this.

1.1
1.10
1.2
1.3
etc


Workaround: We have multiple options to achieve this, this can be done using Event Handlers. Here this is done using multiple calculated field.

The minimum and maximum value would be
1.0 to 99.99.99.99

The logic used to achieve this is to split and pad the values. For instance, converting the following value
1.10.2.3   to 01100203

01.10.02.03 (prefix '0' before each part separated by dot)
01100203 (and  merge all the parts)

For this, I've created 8 calculated columns, you just need to replace the field name SNumber with your field name.

Column Name
Formula
part1
=IF(LEN(LEFT(SNumber,INT(FIND(".",SNumber)-1)))
<2,"0"&LEFT(SNumber,INT(FIND(".",SNumber)-1)),
LEFT(SNumber,INT(FIND(".",SNumber)-1)))
section2
=RIGHT(SNumber,LEN(SNumber)-INT(FIND(".",SNumber)))
part2
=IF(ISNUMBER(INT(FIND(".",section2)-1)),
IF(LEN(LEFT(section2,INT(FIND(".",section2)-1)))<2,
"0"&LEFT(section2,INT(FIND(".",section2)-1)),
LEFT(section2,INT(FIND(".",section2)-1))),
IF(LEN(section2)<2,0&section2,section2))
section3
=RIGHT(section2,LEN(section2)-IF(ISNUMBER(INT(FIND(".",section2))),
INT(FIND(".",section2)),LEN(section2)))
part3
=IF(ISNUMBER(INT(FIND(".",section3)-1)),
IF(LEN(LEFT(section3,INT(FIND(".",section3)-1)))<2,
"0"&LEFT(section3,INT(FIND(".",section3)-1)),
LEFT(section3,INT(FIND(".",section3)-1))),IF(LEN(section3)=0,"00",
IF(LEN(section3)<2,"0"&section3,section3)))
section4
=RIGHT(section3,LEN(section3)-IF(ISNUMBER(INT(FIND(".",section3))),
INT(FIND(".",section3)),LEN(section3)))
part4
=IF(ISNUMBER(INT(FIND(".",section4)-1)),IF(LEN(LEFT(section4,
INT(FIND(".",section4)-1)))<2 amp="" div="" section4="">
LEFT(section4,INT(FIND(".",section4)-1))),IF(LEN(section4)=0,"00",IF(LEN(section4)<2,"0"&section4,section4)))
customSNumber
 =IF(ISNUMBER(part1&part2&part3&part4),part1&part2&part3&part4,0)

Now, just sort the list by customSNumber column

4 comments:

  1. Thank you Prasath. This was incredibly helpful in ordering an outlined list for an FDA submission that included an alphanumeric outline structure.

    One correction: in Part4, there is a "<" symbol missing before the first occurrence of the number 2.

    ReplyDelete
  2. How can you calculate a column on a calculated column. Sharepoint doesnt seem to allow this.

    ReplyDelete
  3. Hello Prasath, your formula helped me very much! Brilliant!!! Thank you a LOT!!!
    Alexander

    ReplyDelete