0

I've got the following file name convention for images in an SP document library -

CQYr UnqID Qual Name

2020 RR0000 Bus Driver Roger Rabbit

I am using the following in a calculate column in that same library to extract the year out of the file name.

=LEFT(Title,(FIND(" ",Title)-1))

I have columns for the other three. How would I modify the calculation for each so that only the UnqID appears in that column, Qual in that column, and Name in that column?

1

For this particular example, you can try the following formulas:

1) CQYr 
=LEFT(Title,4)

2) UnqID
=MID(Title,5,6)

3) Qual
=MID(Title,13,FIND(" ",Title,17)-13)

4) Name
=MID(Title,LEN(LEFT(Title,FIND(" ",Title,17))&" "),20)

enter image description here

Note: Need to validate for other values of [Title], specifically value of Qual in [Title]. If we know the MIN and MAX LEN(Qual) in the entire set of [Title] values, the formulas can be adjusted accordingly.

8
  • Thank you for the detailed reply, it is very helpful. Does the fact that each Title is a different length, beyond the first 10 characters? – Curtis Dec 5 '20 at 13:43
  • For 'Title' the condition is returning 'Firefighter I John Z' or 'Firefighter II Paul'; and for Name its returning 'II Paul Cra' and 'Operator, Pumper Eri'. At the Qual and Name points the numbers are off, but also what is being counted is off. The only sure thing are the 'space' in between each. Thoughts? Is this correctable? Thanks! – Curtis Dec 5 '20 at 14:02
  • The Quals go from as short as 'Medical' to as long as 'Self-Contained Breathing Apparatus Fit Test. Likewise, an entire 'Title can go from as few as 20 characters to almost 100, depending on the length of the Members name and the Qual being referenced. – Curtis Dec 5 '20 at 14:16
  • The problem we're running into to extract [Qual] from [Title] is: no definite number of [space] chars. You have zero to 4 space chars within [Qual]. MID() function can target to extract from a particular [space] char, however, it needs to know the location where to start from. For your data, that start position varies, and that's quite challenging. – Matiur Rahman Dec 5 '20 at 16:07
  • Yes; it is as I assumed. Thank you. What has been provided with the CQYear and UniqueID calculations is enough, for now, too allow for sufficient filtering of existing documents. All new uploads will have Qual and Name added automatically through a flow. – Curtis Dec 5 '20 at 16:35

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.