[已解决]求函数公式提取一列中每行不规则的字符串

whyeyf2015

求助提取的函数公式：一列中每行有一个或两个以上所需的不规则字符串，其中两个以上所需的字符串含某两行以上所需的字符，求用函数批量提取所需字符串（按每行所需字符提取）到另一列中。谢谢！

最佳答案

月排行榜 / 总排行榜

hupin6331

2016-11-27 13:58

B2输入=IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓 ","")&IF(COUNTIF(A2,"*整合哈哈*"),"整合哈哈 ","")&IF(COUNTIF(A2,"*选择*"),"选择 ","")&IF(COUNTIF(A2,"*插入*"),"插入 ","")&IF(COUNTIF(A2,"*格式*"),"格式 ","")&IF(COUNTIF(A2,"*打实*"),"打实 ","") 下拉

跳转到最佳答案楼层

苏子龙

要用的词语，放到一个区域，再用函数提取，可能就可以了

砂海

如果 顺序 不做要求 ,并且 "提取词" 不重复,B2
=LEFT(IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓、","")&IF(COUNTIF(A2,"*整合*"),"整合、",""),LEN(IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓、","")&IF(COUNTIF(A2,"*整合*"),"整合、",""))-1)

把其它的继续往里放 , 橙色的两段是完全相同的

--------------------------
如果 还要保留 出现的 顺序 , 参见 正则表达式

砂海

或者 用辅助单元格

IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓、","")
.
.
.

最后 把这些 单元格 连起来 =LEFT( xxxx,LEN(xxxx)-1)

hupin6331

B2输入=IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓 ","")&IF(COUNTIF(A2,"*整合哈哈*"),"整合哈哈 ","")&IF(COUNTIF(A2,"*选择*"),"选择 ","")&IF(COUNTIF(A2,"*插入*"),"插入 ","")&IF(COUNTIF(A2,"*格式*"),"格式 ","")&IF(COUNTIF(A2,"*打实*"),"打实 ","") 下拉

whyeyf2015

hupin6331 发表于 2016-11-27 13:58
B2输入=IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓 ","")&IF(COUNTIF(A2,"*整合哈哈*"),"整合哈哈 "," ...

提取的字符个数较多的话，公式就特别长吧？能用像数组方法就好！谢谢各位！

whyeyf2015

hupin6331 发表于 2016-11-27 13:58
B2输入=IF(COUNTIF(A2,"*请问乒乒乓乓*"),"请问乒乒乓乓 ","")&IF(COUNTIF(A2,"*整合哈哈*"),"整合哈哈 "," ...

是需要所求的公式，就是提取的字符个数较多的话，公式就特别长吧？能用像数组方法就好！谢谢！

whyeyf2015

请问大侠，是需要的公式，就是提取的字符串个数较多的话，公式就特别长，除“宏”之外还有更简洁一点的吗？