找到我最接近特定字符串的单词？ [英] Find the word which I closest to the particular string?

问题描述

我们进行用户对帐报告，因为我们需要找到为特定用户分配的电子邮件ID。

We do user reconciliation report, in that we need to find the email id assigned for the particular user.

对于ex

For ex

客户报告用户名可能如下

Customer report user name may look like this

Sathish K
Sathya A

但在我们的整合报告中，实际的用户名将会是像这样

But in our consolidation report actual user name will be look like this

Sathish Kothandam
Sathya Arjun

所以我创建了一个宏

Sub test
Dim t as string 
t= "Sathish K"
msgbox(getemailId(t))
End sub

    Dim rng As Range

Function getemailId(Byval findString As String)
    With ActiveWorkbook.Sheets("CONSOLIDATED").Range("B:B")
        Set rng = .find(What:=findString, LookIn:=xlValues)
        If Not rng Is Nothing Then
‘ B – Column contains username C – Email id of the user
            getemailId = rng.offset(0,1).value
        Else
            find1 = 0
        End If
    End With
End Function

我的宏非常适合上述情况，但有时我可能会收到如下所示的用户名。

My macro works perfectly above scenario ,but sometime I may receive user name like below

Satish Kothandam
Sathiya Arjun

但这次它返回0。有没有办法实现我的目标？
希望我解释很好？

But this times It returns 0 . Is there any way to achieve my goal in anyway ? Hope I explained well ?

推荐答案

请看下面的示例代码。

Sub test()

Dim str1 As String, str2 As String
Dim str1c As String, str2c As String

str1 = "Sathish"
str2 = "Satish"

str1c = SOUNDEX(str1)
str2c = SOUNDEX(str2)

MsgBox str1c = str2c

End Sub

Function SOUNDEX(Surname As String) As String
' Developed by Richard J. Yanco
' This function follows the Soundex rules given at
' http://home.utah-inter.net/kinsearch/Soundex.html

    Dim Result As String, c As String * 1
    Dim Location As Integer

    Surname = UCase(Surname)

'   First character must be a letter
    If Asc(Left(Surname, 1)) < 65 Or Asc(Left(Surname, 1)) > 90 Then
        SOUNDEX = ""
        Exit Function
    Else
'       St. is converted to Saint
        If Left(Surname, 3) = "ST." Then
            Surname = "SAINT" & Mid(Surname, 4)
        End If

'       Convert to Soundex: letters to their appropriate digit,
'                     A,E,I,O,U,Y ("slash letters") to slashes
'                     H,W, and everything else to zero-length string

        Result = Left(Surname, 1)
        For Location = 2 To Len(Surname)
            Result = Result & Category(Mid(Surname, Location, 1))
        Next Location

'       Remove double letters
        Location = 2
        Do While Location < Len(Result)
            If Mid(Result, Location, 1) = Mid(Result, Location + 1, 1) Then
                Result = Left(Result, Location) & Mid(Result, Location + 2)
            Else
                Location = Location + 1
            End If
        Loop

'       If category of 1st letter equals 2nd character, remove 2nd character
        If Category(Left(Result, 1)) = Mid(Result, 2, 1) Then
            Result = Left(Result, 1) & Mid(Result, 3)
        End If

'       Remove slashes
        For Location = 2 To Len(Result)
            If Mid(Result, Location, 1) = "/" Then
                Result = Left(Result, Location - 1) & Mid(Result, Location + 1)
            End If
        Next

'       Trim or pad with zeroes as necessary
        Select Case Len(Result)
            Case 4
                SOUNDEX = Result
            Case Is < 4
                SOUNDEX = Result & String(4 - Len(Result), "0")
            Case Is > 4
                SOUNDEX = Left(Result, 4)
        End Select
    End If
End Function

Private Function Category(c) As String
'   Returns a Soundex code for a letter
    Select Case True
        Case c Like "[AEIOUY]"
            Category = "/"
        Case c Like "[BPFV]"
            Category = "1"
        Case c Like "[CSKGJQXZ]"
            Category = "2"
        Case c Like "[DT]"
            Category = "3"
        Case c = "L"
            Category = "4"
        Case c Like "[MN]"
            Category = "5"
        Case c = "R"
            Category = "6"
        Case Else 'This includes H and W, spaces, punctuation, etc.
            Category = ""
    End Select
End Function

这篇关于找到我最接近特定字符串的单词？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！