Results 1 to 3 of 3

Thread: Return Only Different Words In Sentence
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2012
    Location
    Tampa, FL
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return Only Different Words In Sentence

    i have two columns and each column has a text sentence. I just need to create a third column that shows only the words that do not exist in both cells.

    Example;
    If John wants to if john does john if he can might if he can wants to does he can might


    kind thanks,
    Eddie

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Return Only Different Words In Sentence

    Here's a UDF you can try. After you install it, you can use it like a worksheet function as in the example below.
    Sheet10

    ABC
    1If John wants to if john doesjohn if he can might if he canwants to does he can might

    Spreadsheet Formulas
    CellFormula
    C1=notinboth(A1,B1)


    Excel tables to the web >> Excel Jeanie HTML 4

    To install the UDF:
    1. With your workbook active press Alt and F11 keys. This will open the VBE window.
    2. In the project tree on the left of the VBE window, find your project and click on it.
    3. On the VBE menu: Insert>Module
    4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
    5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
    6. Use the UDF as you would any native Excel function.
    7. Make sure you have enabled macros whenever you open the file or the code will not run.
    Code:
    Function NotInBoth(S1 As String, S2 As String) As String
    Dim V1 As Variant, V2 As Variant, i As Long, j As Long, d As Object
    V1 = Split(S1, " ")
    V2 = Split(S2, " ")
    Set d = CreateObject("Scripting.Dictionary")
    For i = LBound(V1) To UBound(V1)
        For j = LBound(V2) To UBound(V2)
            If LCase(V1(i)) = LCase(V2(j)) Then
                Exit For
            ElseIf j = UBound(V2) Then
                If Not d.exists(V1(i)) Then d.Add V1(i), d.Count + 1
            End If
        Next j
    Next i
    For i = LBound(V2) To UBound(V2)
        For j = LBound(V1) To UBound(V1)
            If LCase(V2(i)) = LCase(V1(j)) Then
                Exit For
            ElseIf j = UBound(V1) Then
                If Not d.exists(V2(i)) Then d.Add V2(i), d.Count + 1
            End If
        Next j
    Next i
    If d.Count > 0 Then
        NotInBoth = Join(d.keys, " ")
    Else
        NotInBoth = ""
    End If
    End Function
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,239
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Return Only Different Words In Sentence

    *** Cross-posted on "Excel Forum" forum ***
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •