Return Only Different Words In Sentence

eddiegnz1

New Member
Joined
Jun 5, 2012
Messages
38
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 doesjohn if he can might if he canwants to does he can might

<tbody>
</tbody>


kind thanks,
Eddie
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Here's a UDF you can try. After you install it, you can use it like a worksheet function as in the example below.
Excel Workbook
ABC
1If John wants to if john doesjohn if he can might if he canwants to does he can might
Sheet10


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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,063
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top