按条件截取一段文字中的数据

TaintyF

说明都在附件里。大神求助。

cjc209

颜色的公式 数组 下拉

1. =TEXTJOIN("、",1,IFERROR(IF(MATCH(LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))),LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))),)=ROW($1:$20),LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))),""),""))

复制代码

cjc209

岩性处公式 数组 下拉

1. =TEXTJOIN("、",1,IFERROR(IF(MATCH(MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))+1,9),MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))+1,9),)=ROW($1:$20),MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)),FIND("色",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"岩性：",),"。",),"、",REPT(" ",99)),ROW($1:$20)*99-98,104)))+1,9),""),""))

复制代码

TaintyF

cjc209 发表于 2018-7-10 17:07
岩性处公式 数组 下拉

我这出现错误了。全是name？

cjc209

你的版本不支持TEXTJOIN 这个函数 这个要微软365版本或者WPS 2016最新版的才有 这种连接内存数组 是这个函数的特长 我非常喜欢用

laowuwyx

mikezhan 发表于 2018-7-10 18:10
重要的事情说三遍： 凡是这种复杂的问题，不要用函数，不要用函数，不要用函数！！！！

所言极是！！！！！

准提部林

Function GetTxt(STR$, U%)
Dim T, TT$(1 To 3)
STR = Replace(Replace(Replace(STR, "岩性：", ""), "。", ""), "色", "色、")
For Each T In Split(STR, "、")
　　If InStr("/" & TT(3) & "/", "/" & T & "/") Then GoTo 101 Else TT(3) = TT(3) & "/" & T
　　If Right(T, 1) = "色" Then TT(1) = TT(1) & "、" & T
　　If Right(T, 1) = "岩" Then TT(2) = TT(2) & "、" & T
101: Next
GetTxt = Mid(TT(U), 2)
End Function
　
Ｄ３公式：=GetTxt($A3,COLUMN(A$1))　右拉．下拉