如何使用excel中的一列作为另一列中的搜索参数来仅输出某些字符串 [英] How to use one column in excel as a search parameter in another column to output only certain strings

问题描述

这个问题引发了另一个帖子。 （请参阅搜索对于特定字符串的列，其中字符串在每个单元格中是随机的。）

This question stems off another post I had. (see Search through column in excel for specific strings where the string is random in each cell)

使用上述图像作为参考，我试图通过列B（实际上超过1000行）使用列E作为查找值。最终目标是只是在C列中显示的名称。诀窍是所有随机生成的字符包含名称。下面是我想要的数据表看起来像。公式或模块应该可以工作，但是vlookup和其他查找功能我无法上班。

Using the above image as reference, I am trying to search through column B (actually over 1000 lines) using column E as the "lookup values." The end goal would be for "just" the names to be displayed in column C. The trick is all the randomly generated characters the encompass the names. Below is what I would want the datasheet to look like. A formula or module should work, but the vlookup and other lookup function I can't get to work.

推荐答案

另一种可能性，理解然后最初的帖子，但也可能会有更多的时间消耗（虽然有1000行，我不认为这很重要）在下面。

Another possibility, which may be easier to understand then assylias post initially, but also may be a bit more time consumptive (although with 1,000 rows, I don't think it will matter much) is below.

这个要求您将列E中的范围命名为myNames（或任何您希望的名称，只需更新代码 - 或者，您只需写入范围（E1：E6））。此外，如果从列B中移动随机值，请更新代码中的值。

This requires that you name the range in column E as myNames (or whatever name you wish, just update the code - alternatively, you cuold just write Range("E1:E6")). Also, if you move the random values from column B, update that in the code as well.

Sub findString()

Dim celString As Range, rngString As Range, celSearch As Range, rngSearch As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1") 'change sheet reference to whatever your sheet name is

Set rngString = wks.Range("myNames")
Set rngSearch = Intersect(wks.UsedRange, wks.Range("B1").EntireColumn)

For Each celString In rngString
    For Each celSearch In rngSearch
        If InStr(1, celSearch.Text, celString.Value) > 0 Then
            celSearch.Offset(, 1) = celString.Value
        End If
    Next
Next

End Sub

既然，我也在处理你的原始问题，我建议通过Siddharth的答案，然后运行这个，或者assylias的代码以获取列旁边的名称。您可以按下该按钮，或者只需使用宏对话框运行该宏。

Since, I worked on your original question as well, I would suggest getting the counts through Siddharth's answer and then running this, or assylias's code above to get the names next to the columns. You could put a button the sheet, or just use the Macro dialog box to run the macro.

这篇关于如何使用excel中的一列作为另一列中的搜索参数来仅输出某些字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！