0

I have a document library in which are a couple thousand image files formatted as such:

CQYr UnqID Title FNme LNme Agency

2020 RR0000 Bus Driver Roger Rabbit Federal

I am trying to create several calculated columns in the SP library for filtering purposes. Yep, I've been told that I may not be able to filter using calculated columns; my focus is on the calculation at this point; why is it not working?.

My first attempt was to extract the CQYr from the Title using the following. The result in the calculated column is #Name?.

Any thoughts?

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

Improve this question

3 Answers 3

Reset to default
0

I used your formula as-is, but could not reproduce the error in SharePoint 2013. In your environment, it seems FIND() for a space char is returning error. You can use ISERROR() with IF statement to further troubleshoot.

As-is formula worked for me in SP2013
=LEFT(Title,(FIND(" ",Title)-1))

Use the below to see why FIND() is not successful in your case.
=IF(NOT(ISERROR(FIND(" ",Title))),LEFT(Title,(FIND(" ",Title)-1)),"Not Found")

enter image description here

Improve this answer
11
  • All came back 'Not Found'.
    – Curtis
    Nov 30, 2020 at 18:49
  • That means it's not finding the space char. In your formula, can you replace the space char with "R" or "B" or "D" etc like FIND("B", Title) to see what the output is?
    – Matiur Rahman
    Nov 30, 2020 at 19:24
  • Using your suggestion, rather than adding letters I simply moved the emphasis. Now the conditions says - =LEFT(Title,(FIND("J",Title)-1)). The first item in the list has a Title of 2020JJ1234. Based on the condition the return should be 2020, yes? Its not; still returns #Value!
    – Curtis
    Nov 30, 2020 at 20:13
  • Let's try another example, update your formula to =LEFT("2020JJ1234",(FIND("J","2020JJ1234")-1)) . Try finding value in string "2020JJ1234", see how it works.
    – Matiur Rahman
    Nov 30, 2020 at 20:29
  • I cut and paste this from you comments into the calculation: =LEFT("2020JJ1234" (FIND("J","2020JJ1234")-1)) . The CQYears go from 1987 - 2020. The change you suggested made all lines show the content of "2020" in the CQYear column, regardless of the CQYr in the file names.
    – Curtis
    Nov 30, 2020 at 23:53
0

The error suggests that you are not referring to the correct column, as Matiur Rahman mentioned. If you can return “Not Found” for all columns, it suggests that the formula is working fine.

Change the field name in your formula and see the result.

Example:

enter image description here

The formula I test with:

=LEFT([Filename sample],(FIND(" ",[Filename sample])-1))
Improve this answer
2
  • The only applicable 'field' name revealed to me by SP in the making of the calculated column IS "Title"; even though there is no data in "Title". The data is in the 'Name' column of the view; however, when I go to Library setting "Name" in not a column option - only "Title". If I manually update the "Title" field in properties through 'See Details' the formula fine. No of it makes sense.
    – Curtis
    Dec 1, 2020 at 17:54
  • 1
    @Curtis The Name field created by default is Name (linked to document with edit menu) and cannot be used in Calculated Column formula. Actually, that would be the default behavior for all Office documents. If you check the properties for the files on local drive or in Office clients (not in SharePoint libraries), you will find the Title field empty, which is part of word properties. But your file needs a filename to be able to save, which would be the value of Name field. That is why the Title field can be left empty in the first place.
    – Chelsea_MSFT
    Dec 2, 2020 at 1:55
0

I figured it out! I went to the file name 'details'. In there no file name exists in the 'Title' line, only in the "Name" line. Once I cut and paste the actual file name into the 'Title' field in 'details' the condition worked.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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