CODEWITHSUNDEEP
excel
Aruna Uthayakumar
Aruna Uthayakumar

Reputation: 5

How to find all the strings in a cell that contains a given substring in excel

In excel sheet, each cell contains more than one string. I want to find all the strings that contain the given substring. What is the formula to print all the strings containing the substring in another cell?

E.g: 

  A1-> india china japan

  In A2, i have to print the strings that contains the substring "in" in A1

  A2-> india china

Upvotes: 0

Views: 1110

Answers (2)

tigeravatar
tigeravatar

Reputation: 26660

As an alternate, you can use this UDF:

Public Function GETMATCHES(ByVal strOriginal As String, ByVal strMatch As String) As String

    GETMATCHES = Join(Filter(Split(WorksheetFunction.Trim(strOriginal), " "), strMatch), " ")

End Function

Then in cell A2 would be this formula: =GETMATCHES(A1,"in")

Upvotes: 1

Barry
Barry

Reputation: 3733

I am afraid you cannot do that with the standard Excel worksheet functions. You have to write your own macro for that in VBA like this.

Function matches(ByRef rngCell As Range, ByVal strSearch As String) As String
    Dim astrWords As Variant
    Dim strWord As Variant
    Dim strResult As String

    'Get the value from the cell and split into words based on the space seperator
    astrWords = Split(CStr(rngCell.Value), " ")
    strResult = ""

    'Loop through the words and check if it contains the string that is searched for
    For Each strWord In astrWords
        If InStr(strWord, strSearch) Then
            'Add it to the result if a match is found
            If strResult <> "" Then
                'Add a space when a previous match was found
                strResult = strResult & " "
            End If
            strResult = strResult & strWord
        End If
    Next strWord
    'Return the result
    matches = strResult
End Function

As an example you can use the function in cell A2 as

=matches(A1;"in")

Upvotes: 0

Related Questions