copy text that contains...

[Manolocs]

Hello, I have 5 columns from B,C,D,E,F. Only one contains "<<" followed by text, I want to copy the entire cell that contains "<<" in column H. Any help is more than welcome. Preferably with formula.

 

[jlowens0114]

May I clarify a few things? First, are you saying that any one of the columns (but only one) will have a value of << followed by text? So if these are the columns it might be:
B C D E F G H
Jane Mary <<Alice John Michael Alice
Ed Larry Steve <<Harry Ellen Harry

And do you want the text in column H or the text AND the leading <<? Lastly, just to confirm it really is always the first two characters?

If I'm making the correct assumptions about all of the above, it would be...

=if(right(b1,2)="<<",b1,if(right(c1,2)="<<",c1,if(right(d1,2)="<<",d1,if(right(e1,2)="<<",e1,if(right(f1,2)="<<",f1,"what the heck?")))))

if you want to have the contents if the << cell in column H *without* the <<, that will require a bit more, like so:

=if(right(b1,2)="<<",mid(b1,3,X),if(right(c1,2)="<<",mid(c1,3,X),if(right(d1,2)="<<",mic(d1,3,X),if(right(e1,2)="<<",mid(e1,3,x),if(right(f1,2)="<<",mid(f1,3,X),"what the heck?")))))

where X is the max length of the string you would have. So if it were names and the longest is "<<Kenneth" in B1 you could put mid(b1,3,9). I'm pretty sure you could put 100 for that number and not have problems, but that I'm not 100% sure about.

I'll check back to see if this worked for you or if you have any questions!

 

[Manolocs]

Hi only one of the columns B,C,D,E,F have the "<<" in front of the text so I want the cell with << to be copied to column H.

 

[jlowens0114]

Then one of my two equations ought to do it! It worked for me. Good luck.

 

[AliGW]

Try this:

=INDEX($B$1:$F$1,0,MATCH(">>*",$B$1:$F$1,0))

and if you want to remove the >>:

=SUBSTITUTE(INDEX($B$1:$F$1,0,MATCH(">>*",$B$1:$F$1,0)),">>","")

 

[AliGW]

Sorry - typo! Obviously I meant this:

Try this:

=INDEX($B$1:$F$1,0,MATCH("<<*",$B$1:$F$1,0))

and if you want to remove the >>:

=SUBSTITUTE(INDEX($B$1:$F$1,0,MATCH("<<*",$B$1:$F$1,0)),"<<","")

 

[Manolocs]

Hi AliGW, Works very well thanks

 

[AliGW]

You're welcome! It's a bit shorter than the other suggestion.