SharePoint 2013: My suffix search workaround... calculated column inception

Although a bit frustrating at first, this was somewhat of a fun puzzle.  My client has a SharePoint 2013 site that contains files related to projects that are being managed in an external PPM system.  They want to do the content management for all of the related documents inside SharePoint. Since the core documents would be similar for each project, we went with document sets. We are expecting a very high volume of projects (in the thousands), so a very effective search experience is a must.

First, my standard search enhancements.  A result source for this location that pulls back only items from this library, but just the doc sets.  Since doc sets are considered a list item and also a container, I included this in my query transform: contentclass=STS_ListItem_DocumentLibrary  IsContainer=true.  I updated the search result display template to include a few key pieces of metadata, updated the refiners to leverage the custom metadata fields and turned on the refinement counts.

Since I have done this a few times now, I was able to put that together fairly quickly.  I expected to have it all wrapped up and then move on to the next order of business.  That is when my client presented a request that was a bit challenging to figure out.  The official project numbers they are using from the external system look something like this PRJ00002813.  They would like to be able to search just by the number without the preceding 0's or PRJ designation.  Ok, I thought, a calculated column to take the last 4 digits should work.  Not so fast... the project numbers could have 6 leading 0's, or 7, or 1, or anywhere in between.  They should all have the same total number of digits, but there may also be other project-like items coming in as well.  Those will have a naming convention that like IDEA0001234, which will also have a variable number of 0's.  Well, now this is getting interesting.

I was convinced that a calculated column would be the way to go.  After much time testing out calculations in Excel... many of which I had not tried before, I was able to come up with something that worked.

=RIGHT([TITLE],(LEN([TITLE])+1)-MIN(IFERROR(SEARCH(1,[TITLE]),99),IFERROR(SEARCH(2,[TITLE]),99),IFERROR(SEARCH(3,[TITLE]),99),IFERROR(SEARCH(4,[TITLE]),99),IFERROR(SEARCH(5,[TITLE]),99),IFERROR(SEARCH(6,[TITLE]),99),IFERROR(SEARCH(7,[TITLE]),99),IFERROR(SEARCH(8,[TITLE]),99),IFERROR(SEARCH(9,[TITLE]),99)))

Here is what the calculation is doing from outside in...  

• The RIGHT function returns the last character or characters in a text string, based on the number of characters you specify. 
  Syntax: RIGHT(text,[num_chars]).
  I am using TITLE of the item for the text, and have some nested formulas for the number of characters.
• The LEN function returns the number of characters in a text string.
  Syntax: LEN(text).
  I am taking the number of characters in the title and adding 1.
• This is being subtracted by a MIN function that returns the smallest number in a set of values.
  Syntax: MIN(number1, [number2], ...).
• Within the MIN function, I have an IFERROR function that returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
  Syntax: IFERROR(value, value_if_error).
  If there is an error (because the number doesn't exist in the item title), it returns 99 which won't be the smallest number in this set.
• Finally, we have a SEARCH function which will locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
  Syntax: SEARCH(find_text,within_text,[start_num]).  

I have a series of SEARCH functions, so it will look for each number that is not 0 and return the position of that number, the MIN function then uses the number that is in the leftmost position to subtract from the overall length of the title.  This number is then used by the RIGHT function to return just the numerical digits after the 0's.

Input	Output
PRJ00028987	28987
PRJ00029262	29262
PRJ00000010	10
PRJ00002758	2758
PRJ00002759	2759
PRJ00002760	2760
PRJ00002761	2761
IDEA33	33

The formula looks a bit crazy with the layers of nesting, but it totally works!  I made the calculated column a site column, so it would be searchable.  When I started digging into this, I was hoping to find something I could model it after, but I was unable to find any examples that came close to my scenario.  Hopefully, someone out there will be able to use this for a similar situation.

Thanks for reading!