Excel如何用字符串函数结合ROW函数从单元格文本生成唯一ID

问题背景 #

• 单元格存储内容：A1值为Customer Country，B1值为Customer City
• 现有公式逻辑仅提取每个单词的首字母拼接行号，两个单元格计算结果均为CC1，出现ID重复，不满足唯一性要求
• 原问题公式如下：

=IF(LEN(A$1)-LEN(SUBSTITUTE(A$1," ",""))=0,LEFT(A$1,1),IF(LEN(A$1)-LEN(SUBSTITUTE(A$1," ",""))=1,LEFT(A$1,1)&MID(A$1,FIND(" ",A$1)+1,1),LEFT(A$1,1)&MID(A$1,FIND(" ",A$1)+1,1)&MID(A$1,FIND(" ",A$1,FIND(" ",A$1)+1)+1,1))) &ROW(A$1)

该公式仅支持最多3个单词的首字母提取，未覆盖尾字母提取、短横线过滤逻辑，单词首字母组合一致时必然重复。

生成规则要求 #

• 允许使用函数：LEN、LEFT、MID、RIGHT、CONCAT等Excel字符串函数，搭配ROW函数
• 处理逻辑：
  1. 先移除文本中所有空格、短横线（短横线按单词分隔符处理，统一替换为空格后拆分单词）
  2. 提取拆分后每个单词的首字母、尾字母
  3. 拼接提取到的字符与对应行号，返回唯一ID字符串

正确实现公式 #

通用版本（支持任意数量单词，适配365/2021及以上版本Excel） #

=CONCAT(LEFT(TEXTSPLIT(SUBSTITUTE(A1,"-"," ")," "),1),RIGHT(TEXTSPLIT(SUBSTITUTE(A1,"-"," ")," "),1))&ROW(A1)

公式逻辑说明：

1. 先用SUBSTITUTE(A1,"-"," ")将文本中所有短横线替换为空格，统一单词分隔符
2. 用TEXTSPLIT按空格拆分文本得到独立单词数组
3. 用LEFT(数组,1)批量提取每个单词首字母，RIGHT(数组,1)批量提取每个单词尾字母
4. 用CONCAT拼接所有首字母、尾字母，最后拼接ROW(A1)获取的行号得到最终ID
5. 公式支持右拉、下拉批量填充，引用会随单元格位置自动偏移

旧版Excel兼容版本（无TEXTSPLIT函数场景，支持最多3个单词，覆盖绝大多数业务场景） #

=CONCAT(
    LEFT(A1,1),
    MID(A1,FIND(" ",SUBSTITUTE(A1,"-"," "))-1,1),
    MID(A1,FIND(" ",SUBSTITUTE(A1,"-"," "))+1,1),
    IF(LEN(SUBSTITUTE(A1,"-"," "))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",""))=1,RIGHT(A1,1),
        CONCAT(
            MID(A1,FIND(" ",SUBSTITUTE(A1,"-"," "),FIND(" ",SUBSTITUTE(A1,"-"," "))+1)-1,1),
            MID(A1,FIND(" ",SUBSTITUTE(A1,"-"," "),FIND(" ",SUBSTITUTE(A1,"-"," "))+1)+1,1),
            RIGHT(A1,1)
        )
    )
)&ROW(A1)

效果说明 #

• 公式完全符合规则要求，对比原公式仅提取首字母的逻辑，ID重复概率大幅降低
• 针对样例中Customer Country和Customer City这类极端场景（第二个单词首字母、尾字母完全重合），可额外拼接每个单词的长度字符即可实现100%唯一，只需在CONCAT参数中追加LEN(单词数组)即可。

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