Forum Discussion

mhasanali381's avatar
mhasanali381
Copper Contributor
Jun 03, 2021

Partial String Match Based on Range and Return Matched Word

I'm Trying to Extract the Name from the Description if it matches our Customer Names List. Please see attached image    Here is What I want to do.   If Column B contains any word(Partial String M...
excel
mhasanali381's avatar
mhasanali381
Copper Contributor
Jun 04, 2021

Thanks @HansVogelaar, 

Your formula is working. However, it is returning the same string Many Times. Check attached Screenshot

 

Is there a way to keep only unique words?

 

 

 

 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jun 04, 2021

      mhasanali381 

      Thanks. so we can't use the new UNIQUE function.

      A traditional formula is probably possible, but it'd be complicated. I'd prefer to use a custom VBA function - see the attached sample workbook. You'll have to allow macros when you open it.

      This is the code:

      Function GetMatches(s As String, rng As Range) As String
    Dim dct As Object
    Dim cel As Range
    If s = "" Then Exit Function
    Set dct = CreateObject(Class:="Scripting.Dictionary")
    For Each cel In rng
        If " " & s & " " Like "* " & cel.Value & " *" Then
            dct(cel.Value) = 1
        End If
    Next cel
    GetMatches = Join(dct.Keys, ", ")
End Function

      And the formula:

      =GetMatches([@Desc],TableName[[Column1]:[Column2]])

Resources