如何创建仅打印以点结尾的单词的公式。请参阅下面的示例和图片。
7-5/8 TOWER ASSY. KIT -> ASSY.
90 DEG HYD.FITTING -> HYD.
90 DEG SPLIT FLANGE CDE. -> CDE.
预期结果:
谢谢!
答案1
好吧,它有点长,但这似乎可以解决问题。它将返回单元格中第一个句号之前的单词和句号本身。
=MID(A1,FIND(CHAR(160),SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",CHAR(160),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",""))))+1,FIND(".",A1)-FIND(CHAR(160),SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",CHAR(160),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ","")))))
答案2
例如您的数据在单元格 A27 中。
=MID(LEFT(CONCATENATE(A27),FIND(".", A27)), FIND("!",SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","!",LEN(LEFT(CONCATENATE(A27),FIND(".",A27)))-LEN(SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","")))), (LEN(LEFT(CONCATENATE(A27),FIND(".", A27))))-(FIND("!",SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","!",LEN(LEFT(CONCATENATE(A27),FIND(".",A27)))-LEN(SUBSTITUTE(LEFT(CONCATENATE(A27),FIND(".",A27))," ","")))))+1)
希望它有用。