VBA函数InSTR-如何在搜索的短语中使用星号(和其他字符一样)? [英] VBA function InSTR - How to use asterisk (as any other charakter) in searched phrase?

问题描述

在Excel中，当我们尝试查找某些短语时，可以将星号*放在其他任何字符中.但是如何在VBA宏中执行此操作?例如下面； 我想通过用星号搜索firName的值来找到secName，但是id不起作用.我想问题是VBA认为我想准确地找到*作为常规字符而不是任何其他字符.

In Excel when we try to find some phrase we can put asterisk * inside as any other character. But how to do it inside VBA macro? For example below; I want to find the secName by searching the value of firName with asterisk but id doesn't work. I suppose the problem is that VBA thinks that i want to find exactly * as normal character instead of anything.

Dim firName, secName As String

firName = "Da*"
secName = "Daniel"

search = InStr(1, secName, firName, vbTextCompare)

MsgBox (search)

是否可以按照我描述的方式使用星号*?

Is it possible to use asterisk * in the way I described?

推荐答案

您可以执行模糊搜索，例如:

You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …

…，或者您可以使用 Levenshtein距离找出2个字符串的相似程度，这可能更准确，但需要O(n*m)时间进行计算.所以不要在很长的字符串上使用它.

… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.

Function Levenshtein(str1 As String, str2 As String) As Long
    Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
    Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long

    intLen1 = Len(str1)
    ReDim arrStr1(intLen1 + 1)
    intLen2 = Len(str2)
    ReDim arrStr2(intLen2 + 1)
    ReDim arrLev(intLen1 + 1, intLen2 + 1)

    arrLev(0, 0) = 0
    For i = 1 To intLen1
        arrLev(i, 0) = i
        arrStr1(i) = Mid(str1, i, 1)
    Next i

    For j = 1 To intLen2
        arrLev(0, j) = j
        arrStr2(j) = Mid(str2, j, 1)
    Next j

    For j = 1 To intLen2
        For i = 1 To intLen1
            If arrStr1(i) = arrStr2(j) Then
                arrLev(i, j) = arrLev(i - 1, j - 1)
            Else
                intMini = arrLev(i - 1, j) 'deletion
                If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
                If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion

                arrLev(i, j) = intMini + 1
            End If
        Next i
    Next j

    Levenshtein = arrLev(intLen1, intLen2)
End Function

返回的数字越小，字符串越相似. 例如:

The smaller the returned number is the more similar are the strings. For example:

Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA")   'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1

第二个字符串比第一个字符串更相似.

The second strings are more similar than the first ones.

这篇关于VBA函数InSTR-如何在搜索的短语中使用星号(和其他字符一样)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！