0

I have a document library with a 'Name' column that has files formatted as follows:

 CQYR     UnqID     Title     FirstName     LastName

      2020 RD0000 Teacher  Rodger  Dodger

Is it possible to add/create a calculated column that includes only a portion of the 'Name'?

Say, I want to filter these images by Title (or Last Name, or UnqID), in the 'Name' column, can a formula be written into a calculated column that automatically extracts only the text I need and then use it to filter the images?

4
  • Yes, you can use Calculated Columns with And/Or to Filter items in a SharePoint View. However, based on your explanation, it's not clear what should be included in the calculated column. Is [Name] a calculated column? What values from other columns would you like to be included into this calculated column? Nov 28 '20 at 0:43
  • The Library has a column called 'Name'; that is actually "Title" for formula purposes. In the Title column are the names of the image files. Each image file is formatted as: CQYr UnqID TitleofDoc FirstName LastName or 2020 RD0000 Bus Driver Rodger Dodger Now, let's assume I want to make a calculated column that extracts only the 'CQTitle' from the column "Title", what would that look like? All files names are the same for the first 10 characters from the Left.
    – Curtis
    Nov 28 '20 at 1:47
  • Just to confirm - for this example, is Bus Driver the expected value in the calculated column? And for the first example, it's Teacher, right? How many distinct values are there for CQTitle? Except the space char, are there any other chars separating each part in the formatted file name/title? Does the space char between CQYR and UnqID shown in the examples actually exist in the formatted file name/title or is it shown here intentionally for explaining the file name format? Including space, the first 11 chars will be the same then Nov 28 '20 at 4:07
  • If the intent is to populate the calculated column with CQ Titles from the image file name, yes either and both 'Bus Driver' and 'Teacher' (as examples), as well as 15 other Titles would be potential content options. There is one space between each 'word' in each file name. Yes, the character count (12), left to right, is the same for each file name - 2020 RR0000 . One file name has an hyphen in the Title [Self-Contained Breath...]; and, three have a comma then space at the 28 and 29 character [Driver Operator, Pumper], [, Aerial], [, Mobile Water Supply].
    – Curtis
    Nov 28 '20 at 15:54
0

That puts extracting a part of the [Name] string little difficult. However, I think, we can use Contains clause in the Filter for the view. Even though Filtering on Calculated column worked from me, I'm seeing at here it's not supported. I will see if I can make it work


Updating the response with a basic text extraction formula for the given scenario:

The formula extract string using the following rules:

  1. If a comma (,) is found after the 13th char then extract the string between the 13th char and the comma.
  2. If comma (,) is not found, find the index of the first space char after the 13th char and extract the string between the 13th and the first space char.

Note: Based on the [Title] patterns, this formula can be enhanced further to meet specific conditions using IF-ELSE.

=IF(NOT(ISERROR(FIND(",",Title,13))),MID(Title,13,FIND(",",Title,13)-13),MID(Title,13,FIND(" ",Title,13)-13))

Screenshot showing the output of the formula

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.