Advanced Selection

[Chris_R71]

Have a question. I am a relative novice to Excel and I need to find words on a converted bank registrar with words such as "TRANSFER", "PENSION", "XXSOC SEC", etc and have them copied to a new column. I am not sure how to do this. I have attached screenshot of what I am referencing, and what I might want to do. I manually typed in "XX SOC-SEC" and "PENSION" in bold. Contact me for any udpate.  Thanks.

[#2]

Hi @Chris_R71 

That are many ways to handle such data,

Solution 01

Use Flash Fill where you don't need to write any formula - this article along with video might be helpful for your.

https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f7...

Solution 02 

If you are looking formula version then try this formula.

=CHOOSE(1*COUNTIF(C2,"*TRANSFER*")+2*COUNTIF(C2,"*PENSION*")+3*COUNTIF(C2,"*XXSOC SEC*"),"TRANSFER", "PENSION","XXSOC SEC")

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

[#3]

@Chris_R71 

If your keywords are not limited to the three you gave in your example, you may want to try another option. It avoids hard-coding the key words into the formula. Create a list of key words, elsewhere in your workbook, and enter the following formula in B2:

=IFERROR(INDEX(KeyWords,MATCH(TRUE,(ISNUMBER(SEARCH(KeyWords,C2,1))),0),1),"")

 In this formula, I refer to a Named Range called "KeyWords" where the words/phrases to look for are listed. You can replace it by an absolute reference to the range where you have written the key words (e.g. Sheet2!$A$1:$A$8).

Should you have two key words in the same text (in column C), this formula will return only the first key word found from the list.

[#4]

It worked nicely!

[#5]

@Riny_van_Eekelen Will try this method. Thanks.