Convert Excel formula to UDF function

[shafiey]

Hi, Is it possible to write the formula in cell D2 as a UDF Function?
The file is in Persian language.
I want the formula to search which of the words in column B of the data sheet is in the text of cell C2 of the keywords sheet and return it if it exists.
Many thanks.

Shafiey20230910

 

[OaklandJim]

The UDF below works with English words. I can only guess but it should work if your workbook language is Persian.

Option Explicit

Function StringsInString(psSearchIn, psSearchFor)

'   Array holding words in string to search for.
    Dim sSearchFor() As String
    
'   Array holding words in string to search within.
    Dim sSearchIn() As String
    
'   Used to iterate through words in string to search for.
    Dim iSearchForWords As Long

'   Used to iterate through words in string to search within.
    Dim iSearchInWords As Long

'   Fill array with words in string to search for.
    sSearchFor = Split(" " & psSearchFor, " ")
    
'   Fill array with words in string to search within.
    sSearchIn = Split(" " & psSearchIn, " ")
     
'   Iterate through words to search for.
    For iSearchForWords = 1 To UBound(sSearchFor)
    
'       Iterate through words in string to search within.
        For iSearchInWords = 1 To UBound(sSearchIn)
            
'           Check if word to search for is in string to search within.
            If sSearchFor(iSearchForWords) = sSearchIn(iSearchInWords) _
             Then
                
'               Add space between words found.
                If StringsInString <> "" Then StringsInString = StringsInString & " "
                
'               Add string found to return value.
                StringsInString = StringsInString & sSearchFor(iSearchForWords)
            End If
            
        Next iSearchInWords
    
    Next iSearchForWords
    
'   Remove extra space at the end of the return string, if any.
    StringsInString = Trim(StringsInString)

End Function

 

[shafiey]

The UDF below works with English words. I can only guess but it should work if your workbook language is Persian.

Option Explicit

Function StringsInString(psSearchIn, psSearchFor)

'   Array holding words in string to search for.
    Dim sSearchFor() As String
   
'   Array holding words in string to search within.
    Dim sSearchIn() As String
   
'   Used to iterate through words in string to search for.
    Dim iSearchForWords As Long

'   Used to iterate through words in string to search within.
    Dim iSearchInWords As Long

'   Fill array with words in string to search for.
    sSearchFor = Split(" " & psSearchFor, " ")
   
'   Fill array with words in string to search within.
    sSearchIn = Split(" " & psSearchIn, " ")
    
'   Iterate through words to search for.
    For iSearchForWords = 1 To UBound(sSearchFor)
   
'       Iterate through words in string to search within.
        For iSearchInWords = 1 To UBound(sSearchIn)
           
'           Check if word to search for is in string to search within.
            If sSearchFor(iSearchForWords) = sSearchIn(iSearchInWords) _
             Then
               
'               Add space between words found.
                If StringsInString <> "" Then StringsInString = StringsInString & " "
               
'               Add string found to return value.
                StringsInString = StringsInString & sSearchFor(iSearchForWords)
            End If
           
        Next iSearchInWords
   
    Next iSearchForWords
   
'   Remove extra space at the end of the return string, if any.
    StringsInString = Trim(StringsInString)

End Function

Thank you for your attention.
What formula should I write in cell D2?

 

[OaklandJim]

THIS WORKBOOK shows how to use the UDF that I provided.

 

[shafiey]

THIS WORKBOOK shows how to use the UDF that I provided.

Thank you.
I want it for Persian language. In the Persian language, some words (for example: ی, ها, های and etc.) are suffixes of some other words that are suffixed to them completely or with a half space. This should also be considered. For example, in the words آبها or آب‌ها, the word آب must be derived from both. In my opinion, a column for suffixes should be considered, so that if they are connected to a word, that word is also extracted, and it requires coding, which I do not know.
Sincerely