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.
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§ion2,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"§ion3,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"§ion4,section4)))

customSNumber

=IF(ISNUMBER(part1&part2&part3&part4),part1&part2&part3&part4,0)

Now, just sort the list by customSNumber column
Thank you Prasath. This was incredibly helpful in ordering an outlined list for an FDA submission that included an alphanumeric outline structure.
ReplyDeleteOne correction: in Part4, there is a "<" symbol missing before the first occurrence of the number 2.
Thank you, updated the code.
DeleteHow can you calculate a column on a calculated column. Sharepoint doesnt seem to allow this.
ReplyDeleteHello Prasath, your formula helped me very much! Brilliant!!! Thank you a LOT!!!
ReplyDeleteAlexander