I Need to Copy Paste One Coloum To Another Coloum With Key Words

Hello
I need help Please, It make me confuse. Need single Formula if can. For My Situation.

Rule:
1. Only Coloum With "Good", "Bad" and "Other" Word Will Pick
2, If Theres Two or Three words in same Coloum only pick the First
3, If in One Coloum have same words will pick the First
4, If Theres no Word will fill with Blank

Let's Picture Explain this.
Excel Formula.jpg

I don't know how to put Attachment, it's not working. So I put my sample on Google Drive.
www*drive*google*com/open?id=1TZMNIEFiA9BWbG9yHIm1iMXP1K6YOFwz

Change * with . (dot)

I would go with three formulas for good, bad and other:
B2: =IF(ISNUMBER(SEARCH(" good ", " "&A2&" "))=TRUE,A2,"")
C2: =IF(B2<>"","",IF(ISNUMBER(SEARCH(" bad ", " "&A2&" "))=TRUE,A2,""))
D2 =IF(OR(B2<>"",C2<>""),"",IF(ISNUMBER(SEARCH(" other ", " "&A2&" "))=TRUE,A2,""))

Originally Posted by PaulM100

I would go with three formulas for good, bad and other:
B2: =IF(ISNUMBER(SEARCH(" good ", " "&A2&" "))=TRUE,A2,"")
C2: =IF(B2<>"","",IF(ISNUMBER(SEARCH(" bad ", " "&A2&" "))=TRUE,A2,""))
D2 =IF(OR(B2<>"",C2<>""),"",IF(ISNUMBER(SEARCH(" other ", " "&A2&" "))=TRUE,A2,""))

Ahh, i think this formula not working well. I tried another shot, First at B Row. Different result if u do C Row first.
Try it only B Row first and then C row And then D row.
And i try C row first and then B and then D.
They have diferent result. xD.

Screenshot_4.jpg

ok, looks like it is also dependent on the word position. Add a sample file to figure out a solution.

You CAN attach a file here, but you need to follow these steps:

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi Blek, I managed to find a solution with the help received on the following thread: https://www.excelforum.com/excel-for...ord-occur.html
Using the formulas below, will get you the desired results:

B2: =IF(AND(ISERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")))),"",
IF(IFERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ","")),100)+1,"",
IF(IFERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")),100)+1,"",$A2)))

C2: =IF(AND(ISERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")))),"",
IF(IFERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ","")),100)+1,"",
IF(IFERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")),100)+1,"",$A2)))

D2: =IF(AND(ISERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ",""))),ISERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")))),"",
IF(IFERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(C$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(C$1,$A2))," ","")),100)+1,"",
IF(IFERROR(LEN(LEFT($A2,FIND(D$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(D$1,$A2))," ","")),100)+1>IFERROR(LEN(LEFT($A2,FIND(B$1,$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND(B$1,$A2))," ","")),100)+1,"",$A2)))

Please try at B2 drag across and down

=IFERROR(IF(MATCH(AGGREGATE(15,6,FIND($B$1:$D$1,$A2),1),INDEX(FIND($B$1:$D$1,$A2),),)=COLUMNS($B2:B2),$A2,""),"")

This one needs to press Ctrl+Shift+Enter in Excel, I don't know if it works with WPS Office.
B2
=IFERROR(IF(MATCH(MIN(IFERROR(FIND($B$1:$D$1,$A2),"")),FIND($B$1:$D$1,$A2),)=COLUMNS($B2:B2),$A2,""),"")

Change find to Search

=IFERROR(IF(MATCH(MIN(IFERROR(FIND($B$1:$D$1,$A2),"")),Search($B$1:$D$1,$A2),)=COLUMNS($B2:B2),$A2,""),"")

Originally Posted by Bo_Ry

Change find to Search

=IFERROR(IF(MATCH(MIN(IFERROR(FIND($B$1:$D$1,$A2),"")),Search($B$1:$D$1,$A2),)=COLUMNS($B2:B2),$A2,""),"")

Hmm not working, did i something wrong?

Screenshot_10.jpg

Sorry there are 2 search and need to press Ctrl+Shift+Enter

=IFERROR(IF(MATCH(MIN(IFERROR(Search($B$1:$D$1,$A2),"")),Search($B$1:$D$1,$A2),)=COLUMNS($B2:B2),$A2,""),"")

Originally Posted by Bo_Ry

Sorry there are 2 search and need to press Ctrl+Shift+Enter

=IFERROR(IF(MATCH(MIN(IFERROR(Search($B$1:$D$1,$A2),"")),Search($B$1:$D$1,$A2),)=COLUMNS($B2:B2),$A2,""),"")

WOW You are the best!!!! Very very Bestt! Many Thanks!

A simple question not related to this Thread. If you don't mind.
How to make this Formula not Case-Insensitive?

A2= Super Mouse is DEAD
I wan't Change Mouse with Cat and DEAD with LIFE

I put Formula on B2 --> =SUBSTITUTE(SUBSTITUTE(A2,"DEAD","LIFE"),"Mouse","Cat")

But when A2= Man MOUSE is dead

My Formula not working. How to solve this?

Screenshot_11.jpg

Maybe if you can give another simple formula for this, it will be great. Thank you.

Does this work?

=Proper(SUBSTITUTE(SUBSTITUTE(upper(A2),"DEAD","LIFE"),"Mouse","Cat"))

Upper MOUSE

=Proper(SUBSTITUTE(SUBSTITUTE(upper(A2),"DEAD","LIFE"),"MOUSE","Cat"))

Happy to help.