New Contributor

# 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 Match) that matches with words in Column I and J then return the matched word in Columns G

Currently, My Formula is only finding the word on the same row, not the whole Columns

=IF(OR(COUNTIF(B2,"*"&[@Column1]&"*")),""&[@Column1]&"","")

5 Replies

# Re: Partial String Match Based on Range and Return Matched Word

In G2 as an array formula confirmed with Ctrl+Shift+Enter:

=IF([@Desc]="","",TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&TableName[[Column1]:[Column2]]&" "," "&[@Desc]&" ")),TableName[[Column1]:[Column2]],"")))

where TableName is the name of the table.

# Re: Partial String Match Based on Range and Return Matched Word

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?

# Re: Partial String Match Based on Range and Return Matched Word

Are you using Excel in Microsoft 365?

# Re: Partial String Match Based on Range and Return Matched Word

@Hans Vogelaar No, I'm using office 2019

# Re: Partial String Match Based on Range and Return Matched Word

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]])``