Results 1 to 6 of 6

Thread: Extracting similar words

  1. #1

    Question Extracting similar words



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    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

  4. #4
    Quote Originally Posted by snb View Post
    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

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Did you see that I offered a solution too? Any chance you can test/acknowledge it?


  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    @Milade8000

    I don't think that's a problem.

Posting Permissions

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