☰

简化 Excel 中的多个替代公式

tag-icon • tag-icon microsoft-excel
简化 Excel 中的多个替代公式

如何简化此公式以在 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
  • 用来Insert:Table告诉 Excel 它是一个表格，然后我们给它命名corrections：在此处输入图片描述

然后在主表中使用以下公式：

=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("...", {"\";"/";":";"*";"?";"""";"<";">";"|";"."}))

相关内容