复杂Excel公式用于数据验证失效，单元格计算正常求解

问题1：旧版兼容公式在Excel数据验证中全量失效 #

我需要实现一个数据验证规则，确保指定单元格内以逗号分隔的单词数量不超过4个（最多5个单词），且每个单词字符数不超过5个。为兼容旧版Excel，我用FIND、SUBSTITUTE和MID组合编写了公式，没有使用仅新版支持的TEXTSPLIT函数。

该公式在普通单元格中作为计算式可正常运行，在谷歌表格中也能正常作为数据验证规则生效，但在Excel中用作数据验证时，所有输入都会触发验证错误——哪怕输入单个字符也会报错。

公式如下：

AND(
LEN(
    IFERROR(
        LEFT(
            INDIRECT(ADDRESS(ROW(), COLUMN())),
            FIND(",", INDIRECT(ADDRESS(ROW(), COLUMN()))) - 1
        ),
        INDIRECT(ADDRESS(ROW(), COLUMN()))
    )
) <= 5,

LEN(
    MID(
        INDIRECT(ADDRESS(ROW(), COLUMN())),
        IFERROR(
            FIND(
                "$$$$", 
                IFERROR(
                    SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 1),
                    INDIRECT(ADDRESS(ROW(), COLUMN()))
                )
            ) + 1,
            LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
        ),
        IFERROR(
            IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 2),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ),
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN()))) + 1
            ) - IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 1),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ) + 1,
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
            ),
            0
        )
    )
) <= 5,

LEN(
    MID(
        INDIRECT(ADDRESS(ROW(), COLUMN())),
        IFERROR(
            FIND(
                "$$$$", 
                IFERROR(
                    SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 2),
                    INDIRECT(ADDRESS(ROW(), COLUMN()))
                )
            ) + 1,
            LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
        ),
        IFERROR(
            IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 3),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ),
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN()))) + 1
            ) - IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 2),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ) + 1,
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
            ),
            0
        )
    )
) <= 5,

LEN(
    MID(
        INDIRECT(ADDRESS(ROW(), COLUMN())),
        IFERROR(
            FIND(
                "$$$$", 
                IFERROR(
                    SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 3),
                    INDIRECT(ADDRESS(ROW(), COLUMN()))
                )
            ) + 1,
            LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
        ),
        IFERROR(
            IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 4),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ),
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN()))) + 1
            ) - IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 3),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ) + 1,
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
            ),
            0
        )
    )
) <= 5,

LEN(
    MID(
        INDIRECT(ADDRESS(ROW(), COLUMN())),
        IFERROR(
            FIND(
                "$$$$", 
                IFERROR(
                    SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 4),
                    INDIRECT(ADDRESS(ROW(), COLUMN()))
                )
            ) + 1,
            LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
        ),
        IFERROR(
            IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 5),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ),
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN()))) + 1
            ) - IFERROR(
                FIND(
                    "$$$$", 
                    IFERROR(
                        SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "$$$$", 4),
                        INDIRECT(ADDRESS(ROW(), COLUMN()))
                    )
                ) + 1,
                LEN(INDIRECT(ADDRESS(ROW(), COLUMN())))
            ),
            0
        )
    )
) <= 5,

LEN(INDIRECT(ADDRESS(ROW(), COLUMN()))) - LEN(SUBSTITUTE(INDIRECT(ADDRESS(ROW(), COLUMN())), ",", "")) <= 4

问题2：TEXTSPLIT公式通过Java服务添加后无法生效 #

尝试改用TEXTSPLIT实现相同验证逻辑，但通过Java服务将公式添加到数据验证规则后，规则无法生效。进入Excel的数据验证界面可以看到公式已经存在，但必须手动点击「确定」按钮后，验证规则才能正常激活。

内容的提问来源于stack exchange，提问作者shubham dua