![简化 Excel 中的多个替代公式](https://linux22.com/image/1551119/%E7%AE%80%E5%8C%96%20Excel%20%E4%B8%AD%E7%9A%84%E5%A4%9A%E4%B8%AA%E6%9B%BF%E4%BB%A3%E5%85%AC%E5%BC%8F.png)
如何简化此公式以在 Excel 中删除所有重复的“-”?
单元格 A1:文本------------------文本-文本---文本
单元格 A2:文本----文本--文本---文本
我需要的是:
单元格 A1:文本-文本-文本-文本
我使用的公式:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"--","-"),"--","-"),"--","-"),"--","-")
我想知道是否有一种方法可以使用相同的 SUBSTITUTE 公式来执行类似 4*SUBSTITUTE(A1,"--","-") 的操作,这样就可以完成 4 次工作而不需要庞大的公式。
谢谢
答案1
在单元格 b1 中:=SUBSTITUTE(A1,"--","-")
然后您可以将其复制到 c1、d1、e1 等。右侧的每个单元格都会比前一个单元格少一个 -。添加列,直到处理完所有连字符。
这样,如果您添加的新行中的连字符比以前的行多,那么您只需创建额外的列,而不必更改公式,这会使情况变得混乱。
答案2
单细胞解决方案的一个小技巧。如果A1不包含任何空格,A2进入:
=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"-"," "))," ","-")
这可以处理任意数量的连续破折号:
如果A1确实包含一些空格,因此需要稍微复杂一点的公式!
编辑#1:
如果A1包含空格,则必须首先使用类似以下方式对其进行“保护”:
=SUBSTITUTE(A1," ",CHAR(1))
然后,在最外层的替换中,必须删除“保护”。最终公式为:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",CHAR(1)),"-"," "))," ","-"),CHAR(1)," ")
我承认这很丑陋,但它可以处理无限数量的破折号。
答案3
如果你认为你的需求可能会改变,可以使用查找表
网站exceljet.com有一个很好的方法由 Dave Bruns 处理。当您想在表格中看到您的更正内容,并对其进行编辑或更改时,这种方法非常有用。
它避免了硬编码将所有修正都转化为公式,这可能很难验证(打印在纸上时则更不可能)。
它通过使用INDEX
函数来实现。创建一个查找表:
before | after
--------- | ---------
APPLE | ALICE
BANANA | BOB
CHERRY | CARL
KIWI | KENNY
然后在主表中使用以下公式:
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4))
是的,您可以在公式中使用“回车符”(换行符)。Excel 会忽略它们,但它们会使公式更易于阅读。
数字 1、2、3、4 没有什么神奇之处……它们是函数
corrections
表中的行号INDEX
。查找表中的行数 (4) 与公式中的数字 4 相同。如果您需要更多行,请继续阅读:
根据需要扩展
如果您需要在corrections
表格中添加更多行,这很容易。只需修改替换公式即可。假设您想要添加至 8 行:
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
J2,
INDEX(corrections[before],1),INDEX(corrections[after],1)),
INDEX(corrections[before],2),INDEX(corrections[after],2)),
INDEX(corrections[before],3),INDEX(corrections[after],3)),
INDEX(corrections[before],4),INDEX(corrections[after],4)),
INDEX(corrections[before],5),INDEX(corrections[after],5)),
INDEX(corrections[before],6),INDEX(corrections[after],6)),
INDEX(corrections[before],7),INDEX(corrections[after],7)),
INDEX(corrections[before],8),INDEX(corrections[after],8))
答案4
TEXTJOIN("_", TRUE, TEXTSPLIT("...", {"\";"/";":";"*";"?";"""";"<";">";"|";"."}))