Feb 19 2020 03:06 PM
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.
Feb 19 2020 10:21 PM
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.
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
Feb 20 2020 12:00 AM
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.