Extracting similar words

Milade8080

New member
Joined
Mar 13, 2014
Messages
19
Reaction score
0
Points
0
Hi
Is there a way to make it possible to extract the same words between two sentences
For example, in cell A1 we have : My name is Morteza Ghovati
in cell B1 we have : Morteza Ghovati is My brother
That result is in cell C1 : My-is-Morteza-Ghovati
 
Try adding this UDF to your VB Editor (ALT+F11, INSERT|MODULE).

Code:
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then back in your sheet, try formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH("*"&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)),100))&"*",B1,0)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)),100)),"")," "))," ","-")

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.
 
In C1: =F_snb(A1,B1)

Code:
Function F_snb(sn, sp)
    For Each it In Split(sn)
      If InStr(" " & sp & " ", " " & it & " ") Then c00 = c00 & " " & it
    Next
    For Each it In Split(sp)
      If InStr(" " & sn & " ", " " & it & " ") And InStr(c00 & " ", " " & it & " ") = 0 Then c00 = c00 & " " & it
    Next

    F_snb = Trim(c00)
End Function
 
In C1: =F_snb(A1,B1)

Code:
Function F_snb(sn, sp)
    For Each it In Split(sn)
      If InStr(" " & sp & " ", " " & it & " ") Then c00 = c00 & " " & it
    Next
    For Each it In Split(sp)
      If InStr(" " & sn & " ", " " & it & " ") And InStr(c00 & " ", " " & it & " ") = 0 Then c00 = c00 & " " & it
    Next

    F_snb = Trim(c00)
End Function
Thanks
But there is a problem
In large and small letters ----->> PDF and pdf or Pdf and pDf
 
Did you see that I offered a solution too? Any chance you can test/acknowledge it?
 
Back
Top