英文:
Compare values in one column with another column
问题
A2包含cotton,leather
B2包含Leather,wool,cotton
我需要查看A2中的所有值是否在B2中存在，不考虑大小写或顺序，也不考虑B2中是否有额外的值。
所以我需要一个公式，对于上面的示例，在C2中表示存在。
=IF(AND(ISNUMBER(SEARCH(","&LOWER(TRIM(A2))&",", ","&LOWER(TRIM(B2))&","))), "present", "not present")
这个公式可以按顺序工作。但对于上面的示例，它会显示“not present”。
其他示例如下：
| Column A | Column B | Result |
|---|---|---|
| cotton,leather | Leather,wool,cotton | Present |
| red | red,blue | Present |
| cotton candy,pink | pink,red,cotton candy | Present |
| blue | red,yellow | Not Present |
| Infant,toddler | Baby,Toddler | Not Present |
| Adult,Baby,Taller,Kidder | Adult,Kid | Not Present |
| Steel | Alloy Steel,Wood | Not Present |
英文:
A2 has cotton,leather
B2 has Leather,wool,cotton
I need to see if all values in A2 is present in B2 irrespective of case or order or any extra values in B2.
So i need a formula which says present in C2 for the above example.
=IF(AND(ISNUMBER(SEARCH(","&LOWER(TRIM(A2))&",", ","&LOWER(TRIM(B2))&","))), "present", "not present")
this formula works if it's in order. but for the above example it says not present.
Few other examples are:
| Column A | Column B | Result |
|---|---|---|
| cotton,leather | Leather,wool,cotton | Present |
| red | red,blue | Present |
| cotton candy,pink | pink,red,cotton candy | Present |
| blue | red,yellow | Not Present |
| Infant,toddler | Baby,Toddler | Not Present |
| Adult,Baby,Taller,Kidder | Adult,Kid | Not Present |
| Steel | Alloy Steel,Wood | Not Present |
答案1
得分: 2
使用 <kbd>TEXTSPLIT()</kbd> 和 <kbd>SEARCH()</kbd>
• 在单元格 C2 中使用的公式
=IF(
OR(
ISERROR(
SEARCH(
"&TEXTSPLIT(A2,",")&","","&B2&","))),
"Not ","")
&"Present"
或者，
• 在单元格 C2 中使用的公式
=IF(
AND(
ISNUMBER(
SEARCH(
"&TEXTSPLIT(A2,",")&","","&B2&","))),
"","Not ")
&"Present"
尝试使用 <kbd>FILTERXML()</kbd>
• 在单元格 C2 中使用的公式
=IF(
AND(
ISNUMBER(
SEARCH(
"&FILTERXML("<m><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></m>","//b")&","","&B2&","))),
"Present","Not Present"
)
注意： 根据您的Excel版本，在退出编辑模式时，需要按下 <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd>。
要了解更多关于 <kbd>FILTERXML()</kbd> 的信息，强烈推荐由 JvdV 先生 专门发布的帖子：
Excel - 使用 FILTERXML 从字符串中提取子串
英文:
Using <kbd>TEXTSPLIT()</kbd> & <kbd>SEARCH()</kbd>
• Formula used in cell C2
=IF(
OR(
ISERROR(
SEARCH(
","&TEXTSPLIT(A2,",")&",",","&B2&","))),
"Not ","")
&"Present"
Or,
• Formula used in cell C2
=IF(
AND(
ISNUMBER(
SEARCH(
","&TEXTSPLIT(A2,",")&",",","&B2&","))),
"","Not ")
&"Present"
Try using <kbd>FILTERXML()</kbd>
• Formula used in cell C2
=IF(
AND(
ISNUMBER(
SEARCH(
","&FILTERXML("<m><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></m>","//b")&",",","&B2&","))),
"Present","Not Present"
)
Note: Based on your Excel Version one needs to hit <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> while exiting the edit mode.
To learn more on <kbd>FILTERXML()</kbd> highlighly recommended post exclusively by JvdV Sir:
Excel - Extract substring(s) from string using FILTERXML
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论