Share via

Compare two strings to check if one contains the contents of the other

Anonymous
2013-05-25T16:49:13+00:00

I need to compare two columns, String1 and String2, both containing text to check if the entries in String2 contain the text String1

An example is shown below.

The Results column is populated by comparing each of the String2 entried with all of the String1 entries and if String2 contains exactly the characters in String1 the matching String1 entry is copied into the Results field. I believe each of the entries in String2 will only contain 1 or No entry from the String 1 column.

Can this be done with an built-in function or do I need to create one in VBA? 

String 1
coffee
soda
tea
water
String 2 Results
black tea tea
camomile tea tea
coffee-lavazza coffee
dunkin donuts coffee coffee
green tea tea
illy_coffee coffee
lemon _soda soda
non-sparkiling water water
orange_soda soda
soda-cola soda
sparkling water water
starbucks coffee coffee
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2013-05-25T23:30:39+00:00

    Hi,

    Try this

    1. Suppose String1 entries are in range B2:B5
    2. Suppose string2 entries are in range B8:B19
    3. In cell C8, enter this formula and copy down

    =INDEX($B$2:$B$5,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($B$2:$B$5,B8)),,),0),1)

    Hope this helps.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2013-05-25T20:04:07+00:00

    You can do this with a formula.

    Assume:  String1 refers to:  A1:A4

    String2 range starts in E2

    F2:  =IFERROR(LOOKUP(2,1/SEARCH(String1,E2),String1),"")

    and fill down as far as required.

    0 comments
  3. Anonymous
    2013-05-25T19:18:56+00:00

    Hi

    Try this Assuming you String2 starts from A2 ,

    Enter this formula into B2 & copy down

    =IFERROR(RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),IFERROR(LEFT(A2,FIND("-",A2)-1),IFERROR(RIGHT(A2,LEN(A2)-SEARCH("_",A2)),IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),""))))

    Hope it helps

    Regards

    0 comments
  4. Anonymous
    2013-05-25T17:44:59+00:00

    Hi,

    try this code....

    Sub macro_01()

    Dim rng1 As Range, rng2 As Range, r1 As Range, r2 As Range

    Set rng1 = Range**("A1:A5")  '<<<< String 1 in range a1:a5, changeSet rng2 = Range("B1:B50")** '<<<< String 2 in range b1:b50, change

    For Each r2 In rng2

    r2.Offset(, 1).Value = ""

    For Each r1 In rng1

    If InStr(r2, r1) > 0 Then

    r2.Offset(, 1).Value = r1

    GoTo mynext

    End If

    Next

    mynext:

    Next

    End Sub

    0 comments