Excel:不带嵌套和不带VBA的多个替换

Question

我有一些单元格包含我想要删除的数据。单元格通常如下所示：

alm1 105430_65042M
1993_5689IB
ALM99 3455 344C
0001_4555Alm5

某些单元格(但不是所有单元格)包含类似"almj“的文本，其中"j”是正整数。我想把那部分去掉。我希望输出如下所示：

105430_65042M
1993_5689IB
3455 344C
0001_4555

所以像这样的东西是有效的。

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"alm1",""),"ALM99",""),"Alm5","")

但是对于我的完整数据集，我需要几百个深度的嵌套函数，因为我的"alm“有时是大写字母，有时不是，整数可以从1到100变化。这似乎是一种痛苦的方式。

有没有一种方法可以告诉它查找列中列出的文本(这样我就可以在不同的工作表中有一个列，它来自alm，alm1，...，alm100 )，然后要求它忽略大小写，然后直接替换内容？

我尝试在函数substitute的第二个参数中引用列$M$1:$M$100，但不起作用。

SUBSTITUTE(A2,$M$1:$M$100,"")

其中列M包含"alm“alm1"，...，"alm100”等。

Answer 1

这应该适用于您，它返回了您提供的样本数据的预期结果：

=IF(COUNTIF(A2,"*alm*")=0,A2,TRIM(SUBSTITUTE(A2&" ",MID(A2&" ",SEARCH("alm",A2),FIND(" ",A2&" ",SEARCH("alm",A2))),"")))

Answer 2

Excel的字符串处理能力很差。正则表达式将使这项任务变得容易得多。

在JavaScript中，它很简单：

string.replace(/ *alm[0-9]+ */gi,'')

这将使用全局(g)不区分大小写(i)搜索所有内容：

• 0或更多空格
• 后跟"alm“(在任何情况下)
• 后跟1或更多数字
• 后跟0或更多空格<代码>F210>

你可以把你的数据粘贴到这个小提琴的HTML框中，它会修复它：