Return exact word from text string

---

Ben_Nash wrote:

Hi  v-piga-msft

 

I've provided a data sample to give you an example of the issue I'm trying to work through.

 

Whenver a word in the 'keywords' column appears in the 'messages' column, I want it returned in the 'Keyword return' column. This works fine except I only want distinct words returned.  Note that the keyword 'sink' gets returned for 'Helsinki' but I don't want this returned. Any suggestions would be greatly appreciated.

 

Thanks

 

Ben

 

Sample file

---

Hi Ben,

Here is the text manipulation I did for you.

Cheers,

Swati 

ExactWordMatch = 
 VAR startofstring = SEARCH ( Tweets [Keyword return], Tweets[Messages], 1, 0)
 VAR stringineed = Tweets [Keyword return]
 VAR Istword = IF(Tweets [Keyword return]<>"" && Startofstring=1 ,Trim(Tweets [Keyword return]),"Dummy")
 VAR lastword = TRIM(RIGHT(SUBSTITUTE(Tweets[Messages]," ",REPT(" ",LEN(Tweets[Messages]))),LEN(Tweets[Messages])))
 VAR spaceinthefront = CONCATENATE(" ",Stringineed)
 VAR spaceattheend = CONCATENATE(Stringineed, " ")
 VAR spaceatbothends = CONCATENATE(" ",CONCATENATE(Trim(stringIneed)," "))
 VAR checkstring = IF(stringineed = Istword, spaceattheend, IF( stringineed =lastword, spaceinthefront, spaceatbothends))
 VAR stringIneed2 = IF(SEARCH((Checkstring), (Tweets[Messages]), ,BLANK()),checkstring, "")
 RETURN 
 IF ( ISBLANK(stringIneed2),"" , TRIM(stringIneed2)  )