Write the specific value of the words in the statement (no prefix or suffix)

Hello

First of all, thank you very for your help in advance.

I need you help my friends. Is it possible that could you help me for my excel question. After your help, I wrote my formula in excel that look at the statement and If statement includes any word from my bag word list, then, sum all the values and write it next the statement. But I don't want prefix or suffix which changes my score

Thank you very much

Best Regards,

GERCEK

Your requirement is not clear

My guess is to return the appropriate value (0.5, 0.8) when either LOAD or SCORE are found, ignoring UNLOAD LOADED SCORELESS, etc.

What about adding a space before and after D3 & D4 Cell value?

Replacing A3 cell dot with space in formula?

why not What about adding a space before and after D3 & D4 Cell value?

But How can I add a space before and after D3 & D4 Cell value?


I mean I can't change A text column. It is standart text. But I can change bag word list freely

Okay, we push the said things in formula itself.


Copy paste the below formula in B3 cell

Formula:

=IFERROR(LOOKUP(2,1/SEARCH((" "&$D$3:$D$4&" "),SUBSTITUTE(" "&A3&" ","."," ")),$E$3:$E$4),"")

Drag it down.

Nice one sixth.... I was flaffing around in circles.

I am also worrying about comma's and other characters

I had that bit OK. I was failing to return the scores...

These symbols are all accounted for : .,;?!:*-

=IFERROR(LOOKUP(2,1/SEARCH(" "&$D$2:$D$4&" "," "&SUBSTITUTE(A2,MID(A2,MIN(FIND({".",",",";","?","!",":","*","-"},A2&".,;?!:*-")),1)," ")&" "),$E$2:$E$4),"")

Just for fun, I also made it return present/absent or a score....

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.