通过标点符号查找并获取特定单词,然后从结果中删除标点符号

通过标点符号查找并获取特定单词,然后从结果中删除标点符号

我尝试了各种方法独自解决这个问题,但我束手无策,需要一些帮助。如果你们能给我任何反馈,那就太好了。

我有一个数据集,其每个单元格几乎都遵循以下句子结构:

Twelve little pigs rolled down the hill at Huckleberry, Farms: Citizen from Huckleberry

我正在尝试想出一个 Excel 公式,该公式可以在与数据填充位置不同的单元格中单独捕获单词“Huckleberry”(B2示例中的单元格)。

“Huckleberry” 的结构总是带有“,”并且总是位于带有冒号(:) 的后续单词之前,因此它基本上遵循以下格式:

哈克贝利、农场:
布卢菲尔德、草地:
奶油色、山谷:

使用以下公式我得到的结论非常接近我需要的结果:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),MAX(1,FIND(",",SUBSTITUTE(B2," ",REPT(" ",99)))-50),99))  

但是它保留了逗号,从而给出了“Huckleberry”,而我想要的是“Huckleberry”。

如果可能的话,我更希望使用“Farms:”中的“:”作为起点,从右到左查找“Huckleberry”中的“,”因为有时在“Huckleberry,”中的第一个逗号之前会有逗号。但目前,任何能解决这个问题的公式都将不胜感激。

答案1

这是一个普通的普通公式,可在任何版本的 Excel 中使用:

=MID(B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))+1,FIND(",",B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1)))-FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))-1)

它将正确返回第一的如果该单词后面跟着逗号,则为冒号,而不管数据字符串中有多少个其他冒号前面有单词逗号对。

这个公式的缺陷在于,如果第一个冒号之前的单词之前的单词是不是后面跟着一个逗号,则会返回错误。

解释:

如果使用四个辅助单元格,此公式将更容易理解:

工作表截图

单元格中的公式为:

B3

=LEFT(B2,FIND(":",B2))

B4

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

B5

=SUBSTITUTE(B3," ","§",B4-1)

B6

=FIND("§",B5)

B7

=MID(B2,B6+1,FIND(",",B2,B6)-B6-1)

B3完整的解决方案只是将、B4B5B6的公式代入 的结果B7

笔记:

实际上,保留四个辅助单元,而不是将公式合并为一个,将使公式更易于维护。

更好的方法是将四个辅助单元格公式保留在“开发人员”工作表中,以便于修改,并在生产工作表中使用汇总公式。

答案2

此数组公式将返回带有冒号的单词前带有逗号的单词,无论其前后有多少个逗号:

=CONCAT(IF((RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))=",")*(RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),ROW($1:$40)*99,99)))=":"),LEFT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)),LEN(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))-1),""))

作为数组公式,退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。

CONCAT在 Office 365 Excel 中引入。

该公式在单词之间添加了很多额外的空格,然后使用 mid 循环遍历落在额外空白处的单词并修剪每个部分。

我们测试单词是否以逗号结尾,下一个单词是否以冒号结尾,然后返回减去逗号的单词。

这有一个缺陷,它会返回每个以逗号结尾的单词,而下一个单词以冒号结尾。

在此处输入图片描述

相关内容