Excel:搜索单元格中是否存在多个文本 [英] Excel: Search if multiple texts exist in a cell

问题描述

我在excel中有三列:名字，姓氏，以及包含名字，姓氏和其他字符的第三列.

I have three columns in excel: FirstName, LastName, and a third column that contains first name, last name and maybe other characters.

现在我要做的是在第三列中搜索，如果某个姓氏中同时存在名字和姓氏第三列中的名称与第三列中的第一列和第二列的顺序不同)，返回1.否则，返回0.第三列中的FirstName和LastName的顺序不应影响结果.

Now what I want to do is search in the third column, and if both FirstName and LastName exist in a certain cell (since names in the third column is not in the same order as the first and second column) in the third column, return 1. Otherwise, return 0. The order of FirstName and LastName in the third column should not affect the result.

例如Jennifer，Smith，Smith Jennifer>这将返回1，无论名字/姓氏顺序

E.g. Jennifer, Smith, Smith Jennifer > this will return 1, regardless of the first/last name order

有人知道如何使用公式来实现这一目标吗?

Anyone knows how to accomplish this with a formula?

非常感谢，

为了使我的问题更清楚，这是一个示例.

To make my question more clear, here is an example.

名字，姓氏，名字列表

Jennifer，Smith，Smith Jennifer

Jennifer , Smith , Smith Jennifer

Richard，Borland，Richard Borland(acc)<-此单元格具有其他字符

Richard, Borland, Richard Borland (acc) <-this cell has other characters

Mike，Leanne，Tom Jackson

Mike , Leanne, Tom Jackson

Tom，Jackson和Leanne Mike <-最后两行中的第三列与前两列的顺序不匹配

Tom, Jackson , Leanne Mike <-The third columns in the last two rows do not match the order of the first two columns

推荐答案

NEW

(这将搜索整个第三列)

因此，基本上需要进行一些调整和谷歌搜索，但我想我找到了一个可行的解决方案.这是新公式的简单版本，请注意，此公式是区分大小写，实际上我无能为力，因为您不能说 UPPER(C:C)(UPPEPR()需要一个单元格).但这甚至对于在连接的名称之前，之间或之后具有垃圾的单元格也有效(当然，名称顺序并不重要)

NEW

(this searches the entire 3rd column)

So basically it took some tweaking and googling but I think I found a solution that work. Here is the simple version of the new formula, note that this formula is case sensitive and there really isn't anything I can do about that since you cannot say UPPER(C:C) (UPPEPR() needs a single cell). But this does work even for cells that have junk before, between, or after the joined name (and of course the name order does not matter)

=IF((COUNTIF(C:C,"*"& A1 & "*" & B1 &"*") + COUNTIF(C:C,"*"& B1 & "*" & A1 &"*"))>0,1,0)

当然，正如我在旧答案中所说的那样，请根据需要更改引用.

of course as I said in my old answer, change up the references as needed.

(小编辑)我刚刚意识到这也将检查部分名称.因此，就像某人名为"James Will"和另一个名为"James William"且名称"James William"存在于第3列中一样，它将为"James Will"和"James William"显示1.

(Small edit) I just realized this also will check part of names. So like if someone is named "James Will" and another named "James William" and the name "James William" exists in column 3 it will show 1 for both "James Will" and "James William"

OLD

如果可以在第4列中显示结果，可以使用

If you are okay with having the result in a 4th column you can use a formula like

=IF(ISERROR(FIND(A1 & " " & B1,C1)),IF(ISERROR(FIND(B1 & " " & A1,C1)),0,1),1)

然后自动填充.

当然，如果您的数据不在A-C列中，只需修复单元格引用即可.

Of course if your data is not in columns A-C just fix the cell references.

现在， FIND 公式区分大小写.此示例在名字和姓氏(或姓氏和名字)之间使用1个空格.如果您解释的多余字符恰巧出现在名称之间，则将无法使用.如果是这种情况，则可以使用以下公式:

Now the FIND formula is case sensitive. And this example uses 1 space between the first and last (or last and first) names. If the extra characters you explained happen to appear between the names this will not work. If that is such the case you could then use this formula:

=IF(AND(NOT(ISERROR(FIND(A1,C1))),NOT(ISERROR(FIND(B1,C1)))),1,0)

再次，自动填充，根据需要更改单元格引用.

And again, autofill down, change cell refrences as needed.

这篇关于Excel:搜索单元格中是否存在多个文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！