英文:
Search text and use specific formula for text found
问题
我有一个表格如下：
左侧：我要在单元格中查找的文本列表。右侧：根据我找到的文本将要使用的公式。这个公式对于文本提取是必要的（根据我找到的文本不同）。
问题是：我如何在一个单元格中搜索这个文本列表，然后返回要使用的关联公式？
编辑：我要找的内容如下：
如您所见，每一行都有随机文本，我想提取特定内容。由于Excel的限制，我不能使用If..if..if..。
输入：
lrh34gero egepjpj I28595474 erqm567goh
gerlkq $ONE-234556 ethrh3444rzh
zrlthk 4555 njwhv ùpbozj LFO-FIN-25436545
英文:
I have a table like this
On the left : list of text I'm looking for in a cell. On the right : formula I'll use depending on the text I've found. The formula is necessary for a text extraction (which is not the same regarding the text I've found).
The question is : How can I search for this list of text in one cell and then returning the associated formula to use ?
EDIT : What I'm looking for :
As you can see, I have random texts in each lines, and I want to extract specific content. I cannot use If..if..if.. because of the Excel limitation.
Inputs :
lrh34gero egepjpj I28595474 erqm567goh
gerlkq $ONE-234556 ethrh3444rzh
zrlthk 4555 njwhv ùpbozj LFO-FIN-25436545
答案1
得分: 5
以下是翻译好的部分：
这里有一种选择：
单元格 B2 中的公式：
=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP(D2:D6&"*",x,x,"",2),,-1))))
注意：这假设你的列表值始终位于单词（字符串以空格分割）的开头。如果不是这种情况，仍然要进行拆分，但在查找中使用额外的星号：
=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP("*"&D2:D6&"*",x,x,"",2),,-1))))
英文:
Here is one option:
Formula in B2:
=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP(D2:D6&"*",x,x,"",2),,-1))))
Note: This assumes that your listvalues are always at the start of a word (string get's split on space). If not the case, then still split it but use an extra asterisk in your lookup:
=MAP(A2:A4,LAMBDA(s,LET(x,TEXTSPLIT(s," "),@SORT(XLOOKUP("*"&D2:D6&"*",x,x,"",2),,-1))))
答案2
得分: 3
使用嵌套的BYROW()函数与FILTERXML()。尝试-
=BYROW(B3:B4,LAMBDA(z,TEXTJOIN("",1,BYROW(A2:A4,LAMBDA(x,IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(z," ","</s><s>")&"</s></t>","//s[starts-with(., '\" "&x&"\"')]"),""))))))
要了解有关FILTERXML()的详细信息，请阅读JvdV的这篇文章。
英文:
Use nested BYROW() function with FILTERXML(). Try-
=BYROW(B3:B4,LAMBDA(z,TEXTJOIN("",1,BYROW(A2:A4,LAMBDA(x,IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(z," ","</s><s>")&"</s></t>","//s[starts-with(., '" &x&"')]"),""))))))
To know details about FILTERXML() please read this article from JvdV.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论