Skip to main content
added 470 characters in body
Source Link
Adrian Larson
  • 151.4k
  • 38
  • 247
  • 431

After reviewing the comments, nearly every single clause you implement has an error. It may be more legible to list them here:

  • Your IF syntax needs to be IF(logical_test, value_if_true, value_if_false).
  • Your CONTAINS syntax needs to be CONTAINS(text, compare_text).
  • Composing these functions should yield IF(CONTAINS(text, compare_text), value_if_true, value_if_false)
  • Composing three nested IF statements should yield IF(condition1, value_if_condition1, IF(condition2, value_if_condition2, IF(condition3, value_if_condition3)))
  • You can't call TEXT on a field that is already of the type Text.
  • To return a number, use values like 1 instead of "1".
  • The value "" is not a valid number. Use 0 or null instead.

After reviewing the comments, nearly every single clause you implement has an error. It may be more legible to list them here:

  • Your IF syntax needs to be IF(logical_test, value_if_true, value_if_false).
  • Your CONTAINS syntax needs to be CONTAINS(text, compare_text).
  • Composing these functions should yield IF(CONTAINS(text, compare_text), value_if_true, value_if_false)
  • Composing three nested IF statements should yield IF(condition1, value_if_condition1, IF(condition2, value_if_condition2, IF(condition3, value_if_condition3)))
  • You can't call TEXT on a field that is already of the type Text.
  • To return a number, use values like 1 instead of "1".
  • The value "" is not a valid number. Use 0 or null instead.
added 470 characters in body
Source Link
Adrian Larson
  • 151.4k
  • 38
  • 247
  • 431

Your parentheses are wrong in several places. Proper indentation can make these errors more clear:

IF(
    CONTAINS(
        "Re-Admit...",
        LastName // <-- remove TEXT(LastName...) call if this is a text field
    ), // <-- missing closing parenthesis added
    "1"1, // <-- if your return type is Number, don't wrap the return value in quotes
    IF(
        CONTAINS(
            "Re-Admit 2...",
            LastName // <-- remove TEXT(LastName...) call if this is a text field
        ), // <-- missing closing parenthesis added
        "2"2, // <-- extra closing parenthesis removed
        // ^^^ if your return type is Number, don't wrap the return value in quotes
        IF(
            CONTAINS(
                "Re-Admit 3...",
                LastName // <-- remove TEXT(LastName...) call if this is a text field
            ), // <-- missing closing parenthesis added
            "3"3, // <-- extra closing parenthesis removed
            ""// ^^^ if your return type is Number, don't wrap the return value in quotes
            0 // <-- if your return type is Number, your fallback value should be 0 or null
        ) // <-- close third IF formula
    ) // <-- close second IF formula (added)
) // <-- close first IF formula (added)

I believe the first CONTAINS check will actually catch all the values from the subsequent checks, so you should also reverse the order, checking the "1" case last.

Your parentheses are wrong in several places. Proper indentation can make these errors more clear:

IF(
    CONTAINS(
        "Re-Admit...",
        TEXT(LastName)
    ), // <-- missing closing parenthesis added
    "1",
    IF(
        CONTAINS(
            "Re-Admit 2...",
            TEXT(LastName)
        ), // <-- missing closing parenthesis added
        "2", // <-- extra closing parenthesis removed
        IF(
            CONTAINS(
                "Re-Admit 3...",
                TEXT(LastName)
            ), // <-- missing closing parenthesis added
            "3", // <-- extra closing parenthesis removed
            ""
        ) // <-- close third IF formula
    ) // <-- close second IF formula (added)
) // <-- close first IF formula (added)

I believe the first CONTAINS check will actually catch all the values from the subsequent checks, so you should also reverse the order, checking the "1" case last.

Your parentheses are wrong in several places. Proper indentation can make these errors more clear:

IF(
    CONTAINS(
        "Re-Admit...",
        LastName // <-- remove TEXT(...) call if this is a text field
    ), // <-- missing closing parenthesis added
    1, // <-- if your return type is Number, don't wrap the return value in quotes
    IF(
        CONTAINS(
            "Re-Admit 2...",
            LastName // <-- remove TEXT(...) call if this is a text field
        ), // <-- missing closing parenthesis added
        2, // <-- extra closing parenthesis removed
        // ^^^ if your return type is Number, don't wrap the return value in quotes
        IF(
            CONTAINS(
                "Re-Admit 3...",
                LastName // <-- remove TEXT(...) call if this is a text field
            ), // <-- missing closing parenthesis added
            3, // <-- extra closing parenthesis removed
            // ^^^ if your return type is Number, don't wrap the return value in quotes
            0 // <-- if your return type is Number, your fallback value should be 0 or null
        ) // <-- close third IF formula
    ) // <-- close second IF formula (added)
) // <-- close first IF formula (added)

I believe the first CONTAINS check will actually catch all the values from the subsequent checks, so you should also reverse the order, checking the "1" case last.

Source Link
Adrian Larson
  • 151.4k
  • 38
  • 247
  • 431

Your parentheses are wrong in several places. Proper indentation can make these errors more clear:

IF(
    CONTAINS(
        "Re-Admit...",
        TEXT(LastName)
    ), // <-- missing closing parenthesis added
    "1",
    IF(
        CONTAINS(
            "Re-Admit 2...",
            TEXT(LastName)
        ), // <-- missing closing parenthesis added
        "2", // <-- extra closing parenthesis removed
        IF(
            CONTAINS(
                "Re-Admit 3...",
                TEXT(LastName)
            ), // <-- missing closing parenthesis added
            "3", // <-- extra closing parenthesis removed
            ""
        ) // <-- close third IF formula
    ) // <-- close second IF formula (added)
) // <-- close first IF formula (added)

I believe the first CONTAINS check will actually catch all the values from the subsequent checks, so you should also reverse the order, checking the "1" case last.