string excel在表中搜索字符串，如果为真则返回字符串

excel search for a string in a table and return string if true

提问by david taylor

Column A contains a varying text - around 1000 entries but the same text will appear in different cells.

A 列包含不同的文本 - 大约 1000 个条目，但相同的文本将出现在不同的单元格中。

In a second separate column (Column G) = a separate table, each cell contains one of a number of set text strings (each can be 2 to 5 characters long). Around 20 in all.

在第二个单独的列（列 G）= 一个单独的表格中，每个单元格都包含一组文本字符串中的一个（每个可以是 2 到 5 个字符长）。一共20个左右。

I want to search the start of each cell in column A for each of text strings in column G and if one is present (say G3's contents) I want G3's contents to be shown in column B in the same row as the matched cell from column A.

我想在 A 列的每个单元格的开头搜索 G 列中的每个文本字符串，如果存在（比如 G3 的内容），我希望 G3 的内容与列中匹配的单元格显示在 B 列的同一行中一种。

The matter is complicated by the text to be matched containing both ASC and SC and a further single charcater match being on * (although I can exclude * if this gives a simpler result).

由于要匹配的文本包含 ASC 和 SC 以及在 * 上的进一步单个字符匹配（尽管我可以排除 * 如果这给出了更简单的结果），事情变得复杂了。

Examples

例子

Column Acontains
ASC1 HHHH.........
ASC2 IIII.......
ASC2 AAAA.........
SC2 JJJJ....
TRIAL HOLE........
TRIALHOLE......
OP1 MMMM ...
ASC1COMPLEX
KAN1
PS BAN
*TTC Jiokl jj

A列包含
ASC1 HHHH.......
ASC2 IIII.......
ASC2 AAAA.......
SC2 JJJJ .......试验
孔......
TRIALHOLE......
OP1 MMMM ...
ASC1COMPLEX
KAN1
PS BAN
*TTC Jiokl jj

Column G
ASC1
ASC2
*
OP1
PS
SC2
TRIAL
VERIFY

G栏
ASC1
ASC2
*
OP1
PS
SC2
TRIAL
VERIFY

So column B should show
ASC1
ASC2
ASC2
SC2
TRIAL
TRIAL
OP1
ASC1
0 (or some other error/omit value)
PS
*

所以 B 列应该显示
ASC1
ASC2
ASC2
SC2
TRIAL
TRIAL
OP1
ASC1
0（或一些其他错误/省略值）
PS
*

(How) can this be done? I have tried to adapt various solutions from the internet and failed.

（如何才能做到这一点？我试图适应互联网上的各种解决方案但失败了。

Many thanks DaveT

非常感谢 DaveT

回答by barry houdini

Assuming G2:G20contains the search strings - if you can list them in order of length (shortest first) so given your sample values the list would start with

假设G2:G20包含搜索字符串 - 如果您可以按长度顺序（最短在前）列出它们，那么给定您的样本值，列表将从

* PS OP1 SC2 ASC1 ASC2 TRIAL VERIFY

* PS OP1 SC2 ASC1 ASC2 试用验证

then use this formula in B2 copied down

然后在 B2 中使用这个公式复制下来

=LOOKUP(2^15,FIND(G$2:G$20,A2),G$2:G$20)

=LOOKUP(2^15,FIND(G$2:G$20,A2),G$2:G$20)

G2:G20should be fully populated or adjust the range used - The matching is case-sensitive and #N/A is returned if there is no match

G2:G20应该完全填充或调整使用的范围 - 匹配区分大小写，如果没有匹配则返回 #N/A

If you want to allow blanks in G2:G20 use this version

如果您想在 G2:G20 中允许空白，请使用此版本

=LOOKUP(2^15,FIND(G$2:G$20,A2)/(G$2:G$20<>""),G$2:G$20)

=LOOKUP(2^15,FIND(G$2:G$20,A2)/(G$2:G$20<>""),G$2:G$20)

回答by rwking

This works if you place an asterisk at the end of each string in Column G. The asterisk is a wildcard which allows the MATCH() function to search based on the string plus any number of characters that may follow it.

如果您在 G 列中的每个字符串的末尾放置一个星号，这将起作用。星号是一个通配符，它​​允许 MATCH() 函数根据字符串以及可能跟随它的任意数量的字符进行搜索。

Paste this into cell B1.

将其粘贴到单元格 B1 中。

=IF(ISNA(MATCH($G,A1,FALSE)),"",LEFT($G,SEARCH("*",$G,1)))&IF(ISNA(MATCH($G,A1,FALSE)),"",LEFT($G,SEARCH("*",$G,1)))&IF(ISNA(MATCH($G,A1,FALSE)),"",LEFT($G,SEARCH("*",$G,1)))&IF(ISNA(MATCH($G,A1,FALSE)),"",LEFT($G, SEARCH("*",$G,1)))&IF(ISNA(MATCH($G,A1,FALSE)),"",LEFT($G, SEARCH("*", $G,1)))

You'll have to repeat the "&IF(ISNA(MATCH($G$5,A1, FALSE)), "", LEFT($G$5,SEARCH("*",$G$5,1)))" portion for each cell in Column G where you have a string you want to search.

你必须重复 "&IF(ISNA(MATCH($G$5,A1, FALSE)), "", LEFT($G$5,SEARCH("*",$G$5,1)))" 部分列 G 中的每个单元格，其中您有一个要搜索的字符串。

Then copy the formula in B1 all the way down by double-clicking on the cross-hair in the bottom right hand corner of the cell.

然后通过双击单元格右下角的十字准线将 B1 中的公式一直向下复制。

This will not place anything in the cell if there is not match. It's also not case sensitive, but I'll be the first to admit that if your data is in all caps as it appears, then go for the answer above. It's simpler.

如果不匹配，这将不会在单元格中放置任何内容。它也不区分大小写，但我会第一个承认，如果您的数据显示时全部大写，那么请寻找上面的答案。它更简单。