Excel提取两字符串公共部分与A1剩余部分的公式排障

1. 核心逻辑错误
   原公式使用MATCH(TRUE, EXACT(...), 0)是查找第一个匹配的字符位置，再减1，这与「提取最长公共前缀」的需求完全相反。正确逻辑应是查找第一个不匹配的字符位置，再减1才能得到公共前缀的长度。

2. 数组公式应用错误
   该公式属于数组公式，在非Excel 365/2021版本中，必须通过Ctrl+Shift+Enter组合键输入才能触发逐字符的数组计算。直接下拉普通公式时，无法完成字符级对比，导致结果异常。

3. 易失性函数引发计算异常
   ROW(INDIRECT(...))是易失性函数，批量下拉时会频繁触发工作表重新计算，不仅降低效率，还可能因计算顺序问题返回错误结果。

4. 边界场景未覆盖
   当其中一个字符串是另一个的完整前缀时（比如A1="abc"，B1="abcd"），原公式会返回空值或错误，无法正确提取全部公共前缀。

适用于Excel 365/2021及以上版本（支持动态数组） #

使用更简洁稳定的公式，无需手动触发数组计算：

• C1（提取公共前缀）：

  =LEFT(A1,XMATCH(FALSE,EXACT(MID(A1,SEQUENCE(LEN(A1)),1),MID(B1,SEQUENCE(LEN(A1)),1)),1)-1)
  

  逻辑说明：用SEQUENCE(LEN(A1))生成连续索引（替代易失性的INDIRECT），通过EXACT逐字符对比，XMATCH定位第一个不匹配的位置；若所有字符都匹配，XMATCH返回LEN(A1)+1，减1后正好提取完整公共前缀。
• D1（A1去除公共前缀后的剩余内容）：

  =RIGHT(A1,LEN(A1)-LEN(C1))
  

  原公式可保留，若C1为空（无公共前缀），则返回完整A1内容。

适用于旧版Excel（非动态数组版本） #

需使用数组公式，同时修正逻辑：

• C1（提取公共前缀）：
  输入以下公式后按Ctrl+Shift+Enter，再下拉填充：

  =LEFT(A1,IFERROR(MATCH(FALSE,EXACT(MID(A1,ROW($1:$99),1),MID(B1,ROW($1:$99),1)),0)-1,LEN(A1)))
  

  逻辑说明：用ROW($1:$99)覆盖常规字符串长度（可按需调整99为更大数值），MATCH查找第一个不匹配位置；IFERROR处理A是B前缀的场景（此时MATCH返回#N/A，用LEN(A1)替代以提取完整公共前缀）。
• D1公式同前：

  =RIGHT(A1,LEN(A1)-LEN(C1))
  

额外优化提示 #

• 避免使用易失性函数：用ROW($1:$N)或365版的SEQUENCE替代ROW(INDIRECT(...))，提升计算稳定性和效率。
• 测试边界场景：验证以下情况确保公式正常工作：
  • A1与B1完全相同
  • A1是B1的完整前缀
  • B1是A1的完整前缀
  • 无任何公共前缀

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