Excel - 查找“看起来像”的值 [英] Excel - finding values that "look like"

问题描述

我有一张excel工作簿，大量的床单。在第一页用户中，我有一个用户数据，名字，姓氏，电子邮件等都从CSV文件中整齐地分割。
在其他表格中，我有一些名称，需要用户表格中的电子邮件。

问题是，所有的名称其他工作表都在一个单元格中，同时具有第一个和最后一个名称，在用户工作表中它是拆分的。此外，在其他表格中，它可能被写为麦克安德森，麦克，安德森甚至安德森，迈克。

有谁有想法一个宏/ VBA脚本/ Formular，这将帮助我找到并复制相应的电子邮件？

解决方案

要检查 Mike Anderson ， Mike，Anderson 甚至 Anderson，Mike ，你可以使用 .Find 和 .FindNext 。

看到例如

逻辑：使用Excel的内置 .Find 方法来查找 Mike ，一旦找到，只需检查单元格是否还有安德森

a $ C
Dim oRange As Range，aCell As Range，bCell As Range
Dim ws As Worksheet
Dim SearchString As String，FoundAt作为字符串

错误GoTo Err

设置ws =工作表（Sheet1）
设置oRange = ws.Columns（1）

SearchString =Mike

设置aCell = oRange.Find（What：= SearchString，LookIn：= xlValues，_
LookAt：= xlPart，SearchOrder = xlByRows，SearchDirection：= xlNext，_
MatchCase：= False，SearchFormat：= False）

如果不是aCell是没有
设置bCell = aCell

如果InStr（1，aCell.Value，Anderson，vbTextCompare）Then _
FoundAt = aCell.Address

Do
设置aCell = oRange.FindNext（之后： = aCell）

如果不是aCell不是然后
如果aCell.Address = bCell.Address然后退出Do
如果InStr（1，aCell.Value，Anderson，vbTextCompare ）然后_
FoundAt = FoundAt& ，& aCell.Address
Else
退出Do
结束如果
循环
Else
MsgBox SearchString& 找不到
退出子
结束如果

MsgBox搜索字符串已经找到这些位置：& FoundAt
退出Sub
Err：
MsgBox Err.Description
End Sub

截图

更多 .Find 和 .Findnext here 。

I have an excel workbook with a ton of sheets. In the first sheet "users" i have userdata, firstname, lastname, email, etc. all neatly split from a CSV file. In the other sheets, i have some of the names and need the emails from the "users" sheet.

The problem is, that the names on all the other sheets are all in one cell with both first- and lastname like, and in the users-sheet it's split. Also, in the other sheets it might be written as "Mike Anderson", "Mike, Anderson" or even "Anderson, Mike".

Does anyone have an idea to a macro / VBA script / formular, that would help me find and copy the corresponding emails?

解决方案

To check for Mike Anderson, Mike, Anderson or even Anderson, Mike, you can use .Find and .FindNext .

See this example

Logic: Use the Excel's inbuilt .Find method to find Mike and once that is found, simply check if the cell also has Anderson

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim SearchString As String, FoundAt As String

    On Error GoTo Err

    Set ws = Worksheets("Sheet1")
    Set oRange = ws.Columns(1)

    SearchString = "Mike"

    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        Set bCell = aCell

        If InStr(1, aCell.Value, "Anderson", vbTextCompare) Then _
        FoundAt = aCell.Address

        Do
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                If InStr(1, aCell.Value, "Anderson", vbTextCompare) Then _
                FoundAt = FoundAt & ", " & aCell.Address
            Else
                Exit Do
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
        Exit Sub
    End If

    MsgBox "The Search String has been found these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Screenshot

More on .Find and .Findnext here.

这篇关于Excel - 查找“看起来像”的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！