在excel中搜索字符串在每个单元格中是随机的特定字符串 [英] Search through column in excel for specific strings where the string is random in each cell

问题描述

我正在使用1000行和15列的数据表进行excel。目前，在其中一列中，我有很多数据与人的名字混合在一起（参见下面的例子）。我想看看每个人的名字出现在数据表中的次数，所以我可以在数据表中使用它。名称出现的方式没有特别的格式或顺序。这是随机的有没有办法在excel中编码来搜索整个列，并给出每个人的名字出现次数的计数？

 列D 
 21421Adam14234 
 2323xxx Bob 66 
 23 asjdxx Jacob 665 
 43 Tim 5935539 ​​
 2394Bob 88 
  

经过一些尝试和错误，我可以生成一个名称，每行一个，并将它们放在不同的列中进行比较，如果这样更容易。

解决方案

有VBA标记，但我不知道是否有办法没有它。我已经建立了一个自定义功能。执行以下步骤。

1）从列E1开始列出所需的名称。

2 ）将此功能插入VBA编辑器
A）按Alt + F11
B）单击插入>模块从菜单栏
C）将此代码复制到模块

  Option Explicit 
 
函数findString（rngString As Range，rngSearch As Range）As Long 
 
 Dim cel As Range 
 Dim i As Integer 
 
i = 0 
 
对于每个cel在rngSearch 
如果InStr（1，cel.Text，rngString.Value）> 0然后
 cel.offset（， -  1）= rngString.Value'将单元格中的名称放置在搜索范围右侧
i = i + 1 
 End If 
下一个
 
 findString = i 
 
结束功能
  

3）在F1类型中，以下公式

  = findstring（E1，$ D $ 1：$ D $ 5）
  

4）运行下列公式F列出每个所需名称的计数。

I am working in excel with a datasheet that is 1000 rows and 15 columns. Currently, in one of the columns, I have a lot of data mixed in with people names (see below for an example). I want to see how many times each person's name appears in the datasheet, so I can use it in a pivot table. There is no particular format or order to the way names appear. It is random. Is there a way to code in excel to search through that whole column and give me a count of the amount of times each person's name appears?

Column D
21421Adam14234
2323xxx Bob 66
23 asjdxx Jacob 665
43 Tim 5935539
2394Bob 88

After some trial and error, I can generate a list of names, one per row and place them in a different column for comparison sake, if that makes it easier.

解决方案

You don't have VBA tagged, but I don't know if there is a way to do this without it. I've built a custom function below. To implement it, take the following steps.

1) List desired names starting at column E1.

2) Insert this function into VBA Editor A) Presss Alt + F11 B) Click Insert > Module from menu bar C) Copy this code into Module

Option Explicit

Function findString(rngString As Range, rngSearch As Range) As Long

Dim cel As Range
Dim i As Integer

i = 0

For Each cel In rngSearch
    If InStr(1, cel.Text, rngString.Value) > 0 Then 
        cel.offset(,-1) = rngString.Value 'places the name in cell to right of search range
        i = i + 1
    End If
Next

findString = i

End Function

3) In F1 type the following formula

=findstring(E1,$D$1:$D$5)

4) Run the formula down column F to get the count of each desired name.

这篇关于在excel中搜索字符串在每个单元格中是随机的特定字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！