按条件截取所需字符串

阿红红123

大佬们，有两个截取问题，一个是按指定的单词截取指定单词和下一个单词（空格隔开），一个是截取指定*号位置之间的内容，如图：

函数二把刀

这对pq来说就是小菜一碟,不知道支不支持?excel2016版本以上

满坛皆为吾师

B3下拉

1. =TRIM(LEFT(SUBSTITUTE(MID(A3,MIN(IFERROR(FIND({"why","on","for"},A3),99)),99)," ",REPT(" ",99),2),99))

复制代码

G3下拉

1. =TRIM(RIGHT(SUBSTITUTE(F3,"*",REPT(" ",9)),9))

复制代码

H3下拉

1. =TRIM(LEFT(RIGHT(SUBSTITUTE(F3,"*",REPT(" ",45)),90),45))

复制代码

阿红红123

函数二把刀 发表于 2021-3-1 20:21
这对pq来说就是小菜一碟,不知道支不支持?excel2016版本以上

用wps和office365，想学pq,大佬不知道有没有资料推荐一下

limonet

问题1解1=CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},""))&MID(A3,FIND(" ",A3,FIND(CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},"")),A3)),
FIND(" ",A3,FIND(" ",A3,FIND(CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},"")),A3))+1)-FIND(" ",A3,FIND(CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},"")),A3))+1)
问题1解2=CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},""))&" "&INDEX(FILTERXML("<a><b>"&SUBSTITUTE(A3," ","</b><b>")&"</b></a>","a/b"),MATCH(CONCAT(IF(ISNUMBER(FIND({"why";"on";"for"},A3)),{"why";"on";"for"},"")),FILTERXML("<a><b>"&SUBSTITUTE(A3," ","</b><b>")&"</b></a>","a/b"),)+1)

limonet

问题二——1=MID(F3,FIND("#",SUBSTITUTE(F3,"*","#",LEN(F3)-LEN(SUBSTITUTE(F3,"*",))))+1,99)
问题二——2=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(F3,"*","</b><b>")&"</b></a>","a/b"),LEN(F3)-LEN(SUBSTITUTE(F3,"*",)))

mmlzh

b3=TRIM(LEFT(SUBSTITUTE(MID(A3,MIN(FIND({"why","on","for"},A3&"whyonfor")),99)," ",REPT(" ",99),2),99))

g3=TRIM(LEFT(RIGHT(SUBSTITUTE($F3,"*",REPT(" ",99)),COLUMN(A:A)*99),99))
右拉

我走我流

学PQ的话去  PQFANS点COM  基础知识和案例分析，我刚去了下，今天好像翻车了

randysjf

=MID(A3,MAX(IFERROR(FIND({"why","on","for"},A3),0)),MAX(IFERROR(FIND(" ",A3,FIND({"why","on","for"},A3)+4)-FIND({"why","on","for"},A3),0)))
=RIGHT(A10,LEN(A10)-MAX(IFERROR(SEARCH("~*",A10,ROW($1:$100)),0)))
=MID(A10,LARGE(UNIQUE(IFERROR(SEARCH("~*",A10,ROW($1:$100)),0)),ROW($2:$2))+1,MAX(IFERROR(SEARCH("~*",A10,ROW($1:$100)),0))-1-LARGE(UNIQUE(IFERROR(SEARCH("~*",A10,ROW($1:$100)),0)),ROW($2:$2)))

hcm19522

问题一=TRIM(LEFT(SUBSTITUTE(MID(A3,LOOKUP(99,FIND({"why","on","for"},A3)),99)," ",REPT(" ",99),2),99))