Forum Discussion

Ben_Nash's avatar
Ben_Nash
Frequent Visitor
8 years ago

Return exact word from text string

Hi   I am running a list of keywords against text and getting keywords returned in a column if they appear in the text. The below formula is working fine except I'm also getting words returned even...
Need Help
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

Hi  Ben_Nash,

 

I have made a test with your formula, it seems that it works as expected.

 

 

More details you could have a reference of the attachment.

 

If you still need help, please share some data sample which could reproduce your scenario and your desired output, so that we can help further investigate on it?

 

Best regards,

Cherry

Ben_Nash's avatar
Ben_Nash
Frequent Visitor
8 years ago

Hi  

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Ben_Nash wrote:

    Hi  

     

    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)  )