0

I'm trying to create a complex calculated column where it looks through the title of a document to pick out keywords and display a code based off which set of keywords it finds. For example, if the title includes the phrase "Townfield" then I want it to show "11T".

In basic practice I can get this working using nested IF(ISNUMBER(FIND, however I want to be bomb proof so would like it to recognise these other versions of they keyword and also show "11T" = Townfield, townfield, TOWNFIELD, 11 T, ISLEY. I know i need to include an OR statement.

I thought I had found the right formula in the below using IF(OR( then repeated ISNUMBER(FIND statements till the end of the set of key words where "XXX" would be the nesting location where i would start IF again for the next set of keywords.

=IF(OR(ISNUMBER(FIND("Townfield",[Title])),(ISNUMBER(FIND("TOWNFIELD",[Title])),(ISNUMBER(FIND("11T",[Title]))),"11T","XXX")

However I'm just getting syntax errors - even when I try just the below on its own no nesting its throwing an error.

What am I doing wrong please?

2 Answers 2

0

As the error suggests, you have some issues with your syntax. In my opinion, this can happen really easily, due to how the SharePoint calculated column formula input works.

I'd suggest using a tool like Notepad++ to write your formulas and use indentation, which will make it much easier to read and therefore, easier to identify where the syntax issues are.

Indented Version of your formula:

=IF(
    OR(
        ISNUMBER(FIND("Townfield",[Title])),
        (ISNUMBER(FIND("TOWNFIELD",[Title])),
        (ISNUMBER(FIND("11T",[Title]))),
    "11T",
    "XXX"
)

Which quickly shows that you have some additional brackets that are causing the problem.

Modified formula which should work:

=IF(
    OR(
        ISNUMBER(FIND("Townfield",[Title])),
        ISNUMBER(FIND("TOWNFIELD",[Title])),
        ISNUMBER(FIND("11T",[Title]))
        ),
    "11T",
    "XXX"
)
0

You can use SharePoint calculated column formula like below for your requirements:

=IF(
    OR(
        ISNUMBER(FIND("Townfield",[Title])),
        ISNUMBER(FIND("townfield",[Title])),
        ISNUMBER(FIND("TOWNFIELD",[Title])),
        ISNUMBER(FIND("11 T",[Title])),
        ISNUMBER(FIND("ISLEY",[Title]))
    ),
    "11T",
    IF(
        OR(
            ISNUMBER(FIND("Test",[Title])),
            ISNUMBER(FIND("test",[Title]))
        ),
        "Test",
        "XXX"
    )
)

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Output:

enter image description here

Your Answer

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

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