Excel TEXT函数含M/N返回#VALUE!错误的原因及解决方法

问题场景 #

有包含邮箱列（A列）和序列号列（B列）的表格，需获取每个邮箱对应的最大序列号。使用数组公式：

=TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\VUAM00000")

当格式字符串包含M或N时返回#VALUE!错误，但简化后的=MAX(IF($A$1:$A$100=K3, RIGHT($B$1:$B$100, 5)+0))可正常执行，且必须保留VUAM的序列号命名规则。

错误原因 #

Excel的TEXT函数格式代码中，M、N是内置的日期格式代码：

• M：显示月份缩写（如Jan）
• N：显示月份全称（如January）
  原公式中的\VUAM仅对V、U、A做了转义（\是转义符），但M未转义，会被Excel识别为日期格式代码。而MAX返回的是纯数字，没有对应的日期上下文，因此触发#VALUE!错误。

解决方法 #

方法1：转义所有字母 #

对格式字符串中的每个字母都添加转义符，确保Excel将其视为普通文本：

=TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "\V\U\A\M00000")

方法2：用连接符替代TEXT的格式拼接 #

先通过TEXT补全数字的前置零，再与固定前缀VUAM拼接，完全避开格式代码冲突：

="VUAM"&TEXT(MAX(IF($A$1:$A$100=A4, MID($B$1:$B$100, 5, 5)+0)), "00000")

注：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入；新版Excel支持动态数组，直接回车即可。

内容的提问来源于stack exchange，提问作者Mohamed Maghrabi