如何在Excel中用Substitute/Replace按顺序替换指定字符？

需求回顾 #

A1内容：Abba was a great band.
A3内容：123456
目标：将A1中按顺序出现的6个"a"（含大写"A"）依次替换为A3中对应位置的字符，最终得到1bb2 w3s 4 gre5t b6nd.

方法一：嵌套SUBSTITUTE函数（兼容所有Excel版本） #

由于从前往后替换会打乱后续字符的出现次数，需从最后一次出现的"a"开始往前替换，确保每次替换的目标位置准确。公式如下：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",MID(A3,6,1),6),"a",MID(A3,5,1),5),"a",MID(A3,4,1),4),"a",MID(A3,3,1),3),"a",MID(A3,2,1),2),"a",MID(A3,1,1),1)

• 核心逻辑：通过SUBSTITUTE(原文本, 替换目标, 替换内容, 第N次出现)指定替换第N次的"a"，从第6次到第1次依次匹配A3的对应位字符。
• 扩展至19次替换：只需继续嵌套SUBSTITUTE函数，直到覆盖第19次出现的"a"，替换内容对应MID(A3,19,1)到MID(A3,1,1)即可。

方法二：数组公式（Excel 365/2021及以上版本适用） #

无需手动嵌套函数，自动识别所有"a"的出现顺序并完成替换，公式如下：

=TEXTJOIN("",,IF(MID(A1,SEQUENCE(LEN(A1)),1)="a",MID(A3,COUNTIF(MID(A1,SEQUENCE(ROW(A1:INDEX(A1:A1,LEN(A1))),1),1),"a"),1),MID(A1,SEQUENCE(LEN(A1)),1)))

• 核心逻辑：
  1. SEQUENCE(LEN(A1))生成A1所有字符的位置序列；
  2. COUNTIF(...)统计每个位置之前（含当前）"a"的出现次数，作为A3的字符提取索引；
  3. IF判断当前字符是否为"a"，是则取A3对应位置字符，否则保留原字符；
  4. TEXTJOIN将所有字符拼接为最终文本。
• 优势：无论A1中"a"的出现次数是多少（只要A3字符长度足够），无需修改公式即可适配。

注意事项 #

• 若需区分大小写，可将公式中的"a"判断改为EXACT(MID(A1,SEQUENCE(LEN(A1)),1),"a")（方法二需对应调整）；
• 确保A3的字符长度不小于A1中"a"的出现次数，否则超出部分的"a"会被替换为空。

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