• 589K Members
• 5,214 Online
• 714K Conversations

Highlighted
New Contributor

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.

4 Replies
Highlighted

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

Highlighted

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.

Highlighted