Extracting string from a file name RRS feed

  • Question

  • Hello,

    I have a legacy (read: I didn't build it) SharePoint list  that includes some validation when uploading files that's giving me some trouble.

    Basically, our users are required to add files to a list in a certain filename format and based on the naming convention are approved/rejected and routed to the appropriate location.

    One of the validations looks at a section of the file name and compares it to a folder name in the library.

    For example, the file name format is XX_AAA_999_2014_05.xlsx and that matches on the folder name of /submissions/2014_05

    Currently the rule says look at the last 7 characters of the folder and the 7 characters starting at position 12 of the filename and make sure they match.

    The problem is the 999 in the example above is a sequential identifier to the project a file is associated with... e.g. they range from project 000 to project 999. We've now hit project 1000 so file being added for project 1000 (and beyond) fails because the starting position has shifted one spot. (Note: we have active 3 digit projects so I cannot simply change that to be position 13... not to mention what that does to my history).

    So, my task is to come up with something that can accomodate 3 or 4 digit numbers.

    I'm trying to stick as closely to the original setup so I don't mess up the history so I'm looking at other methods of getting to the same data in the string.  Another problem is that the file names include the extension and the extension can be 3 (pdf) or 4 (xlsx) characters long.

    I've tried this:  =LEFT([Source File Name],SEARCH(".",[Source File Name])-1) but that brings back everything in front of the period and I need just the 7 preceeding characters.  Is there a way to limit the number of chars a LEFT() function returns?

    In a nutshell, the 4 variations of file names are as follows of which I need to extract the bolded section.:

    1. ZZ_AAA_999_2014_05.xls
    2. ZZ_AAA_999_2014_05.xlsx
    3. ZZ_AAA_1000_2014_05.xls
    4. ZZ_AAA_1000_2014_05.xlsx



    Thursday, June 5, 2014 9:20 PM


  • Hi Kevin,

    I think =RIGHT(LEFT([Source File Name],SEARCH(".",[Source File Name])-1),7) should work since this would only return only 7 characters from the right of th text without the period.

    Hope this helps


    Friday, June 6, 2014 11:07 AM

All replies