1

I am looking to find a way to extract text from the TITLE of an item when that text value could be different.

Example:
TITLE = N10 2019-2020 Kilo Project R Sec All Data
Calculated column called Section to pull out the section information (R Sec) is: 

=TRIM(MID(Title,SEARCH("sec",Title)-2,5))

This results in "Kilo Project; however the title of the section can be different in 4 ways
1. sec
2. national
3. set's
4. SHQ

They all have to be entered into the same calculated column Section so I thought about making a nested IF and haven't been successful in find a solution as everytime I try to I will get an error with the value (testing in excel).

Thanks in advance.

Improve this question
4
  • Can you give a couple examples of how the title can be made? Is it always one character like R before the section? What about poor user input? Why can't the user just be prompted to put the data in fields themselves? Commented Sep 3, 2019 at 15:01
  • We are trying to automate the upload process (add multiple documents at the same time). The first part of the title indicates the time period N01-N52 and multiple documents get uploaded each time, this is mainly for automation. Other titles could be "N45 2019-2020 Kilo Project national All Data" or "N45 2019-2020 Kilo Project set's sec All Data" or "N22 2019-2020 Jasper Project SHQ Budget Data". The Project name "Jasper Project" is a document set, but the section is different always. the last part about the data I can draw from a RIGHT function. Thanks Commented Sep 3, 2019 at 15:28
  • Okay. I've got an answer that may be satisfactory in the works. Please let me know how it works for you and we can troubleshoot any issues you have. Commented Sep 3, 2019 at 15:29
  • Thank you @KGlasier Commented Sep 3, 2019 at 15:32

1 Answer 1

Reset to default
0

Without any additional knowledge (ie a response from my comment above), this is the best I could come up with.

I would like to recommend using default column settings based on folders as an alternative solution to prevent as much human error in title names. This pushes values to specific columns in your files depending on what folder they're placed into. From there you can have views that strip your documents out of folders but still hold the necessary metadata involved. A brief tutorial can be found here.

On with the real answer:

The issue you were primarily having is that Search returns an error when the value is not found. So doing a nested if statement requires checking for said errors in the format 

=IF(
    NOT(ISERR(<search-function>)),
    <if-true>,
    <if-false>
)

Also a bit more information filtering was added in my below function. Primarily searching for the word you specified by adding a space on either end. Otherwise words like national would match with nationalist which may not be desired and give the wrong result. Then I made the whole section UPPERCASE so any filtering in SharePoint can be done with a single filter option since you won't have to filter by sec, Sec, SEc, etc due to human error.

Excel string (Where A1 was the title of the document):

=UPPER(
    IF(
        NOT(ISERR(SEARCH(" sec ",A1))),
        TRIM(MID(A1,SEARCH(" sec ",A1)-2,6)),
        IF(
            NOT(ISERR(SEARCH(" national ",A1))),
            TRIM(MID(A1,SEARCH(" national ",A1)-2,11)),
            IF(
                NOT(ISERR(SEARCH(" set's ",A1))),
                TRIM(MID(A1,SEARCH(" set's ",A1)-2,8)),
                IF(
                    NOT(ISERR(SEARCH(" shq",A1))),
                    TRIM(MID(A1,SEARCH(" shq ",A1)-2,6)),
                    ""
                )
            )
        )
    )
)

SharePoint translation:

=UPPER(
    IF(
        NOT(ISERR(SEARCH(" sec ",Title))),
        TRIM(MID(Title,SEARCH(" sec ",Title)-2,6)),
        IF(
            NOT(ISERR(SEARCH(" national ",Title))),
            TRIM(MID(Title,SEARCH(" national ",Title)-2,11)),
            IF(
                NOT(ISERR(SEARCH(" set's ",Title))),
                TRIM(MID(Title,SEARCH(" set's ",Title)-2,8)),
                IF(
                    NOT(ISERR(SEARCH(" shq",Title))),
                    TRIM(MID(Title,SEARCH(" shq ",Title)-2,6)),
                    ""
                )
            )
        )
    )
)
Improve this answer
3
  • 1
    this worked, I'll adjust the numbering a bit, but the logic behind it is working as intended. Thank you for your help! Commented Sep 3, 2019 at 15:46
  • No problem! For future situations I would suggest looking at the link I posted above. It may suit your needs without having a complicated title field in other situations. Commented Sep 3, 2019 at 15:48
  • I've bookmarked that link for future use. The rest of the important metadata will be set automaticly, it's just there are over dozens of documents a week that get uploaded all at the same time and I wanted to automate the basics from the title. Cheers! Commented Sep 3, 2019 at 15:55

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.