Return exact word from text string

[Ben_Nash]

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 when they are part of another word/string. For example if the text contains the keyword 'got', I'm also getting a return on 'forgot' when I only want a return on 'got'. Any suggestions on what to add to the below formula would be appreciated!

Keyword return = FIRSTNONBLANK(FILTER(VALUES('Reputation Keywords'[Keyword]),
SEARCH('Reputation Keywords'[Keyword],Messages[Message],
1,
0
)
),1)

Thanks

[v-piga-msft]

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]

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

[swatirana2010]

---

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

[Ben_Nash]

Hi Cherry

Thanks for your reply and testing.

An example of the problem I’m facing is with a keyword such as ‘taint’. I’m getting a return on the following text ‘If you like long lines, hidden fees, militaint staff, and all around terrible customer experiences’ whereas the formula should be ignoring this text and only returning on 'taint'

Thanks

Ben