我尝试了各种方法独自解决这个问题,但我束手无策,需要一些帮助。如果你们能给我任何反馈,那就太好了。
我有一个数据集,其每个单元格几乎都遵循以下句子结构:
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
完整的解决方案只是将、B4
、B5
和B6
的公式代入 的结果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 循环遍历落在额外空白处的单词并修剪每个部分。
我们测试单词是否以逗号结尾,下一个单词是否以冒号结尾,然后返回减去逗号的单词。
这有一个缺陷,它会返回每个以逗号结尾的单词,而下一个单词以冒号结尾。