Home

Advanced Selection

%3CLINGO-SUB%20id%3D%22lingo-sub-1183398%22%20slang%3D%22en-US%22%3EAdvanced%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183398%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20a%20question.%20I%20am%20a%20relative%20novice%20to%20Excel%20and%20I%20need%20to%20find%20words%20on%20a%20converted%20bank%20registrar%20with%20words%20such%20as%20%22TRANSFER%22%2C%20%22PENSION%22%2C%20%22%3CFONT%3EXXSOC%20SEC%3C%2FFONT%3E%22%2C%20etc%20and%20have%20them%20copied%20to%20a%20new%20column.%20I%20am%20not%20sure%20how%20to%20do%20this.%20I%20have%20attached%20screenshot%20of%20what%20I%20am%20referencing%2C%20and%20what%20I%20might%20want%20to%20do.%20I%20manually%20typed%20in%20%22XX%20SOC-SEC%22%20and%20%22PENSION%22%20in%20bold.%20Contact%20me%20for%20any%20udpate.%26nbsp%3B%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1183398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183812%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183812%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563599%22%20target%3D%22_blank%22%3E%40Chris_R71%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20are%20many%20ways%20to%20handle%20such%20data%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2001%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EUse%20Flash%20Fill%20where%20you%20don't%20need%20to%20write%20any%20formula%20-%20this%20article%20along%20with%20video%20might%20be%20helpful%20for%20your.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fusing-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fusing-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2002%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20looking%20formula%20version%20then%20try%20this%20formula.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCHOOSE(1*COUNTIF(C2%2C%22*TRANSFER*%22)%2B2*COUNTIF(C2%2C%22*PENSION*%22)%2B3*COUNTIF(C2%2C%22*XXSOC%20SEC*%22)%2C%22TRANSFER%22%2C%20%22PENSION%22%2C%22XXSOC%20SEC%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_3e21c3.png%22%20style%3D%22width%3A%20808px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172174iBFE169DE8F71717D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_3e21c3.png%22%20alt%3D%22Snag_3e21c3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%20%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelexciting.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.ExcelExciting.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183937%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563599%22%20target%3D%22_blank%22%3E%40Chris_R71%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20keywords%20are%20not%20limited%20to%20the%20three%20you%20gave%20in%20your%20example%2C%20you%20may%20want%20to%20try%20another%20option.%20It%20avoids%20hard-coding%20the%20key%20words%20into%20the%20formula.%20Create%20a%20list%20of%20key%20words%2C%20elsewhere%20in%20your%20workbook%2C%20and%20enter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(KeyWords%2CMATCH(TRUE%2C(ISNUMBER(SEARCH(KeyWords%2CC2%2C1)))%2C0)%2C1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BIn%20this%20formula%2C%20I%20refer%20to%20a%20Named%20Range%20called%20%22KeyWords%22%20where%20the%20words%2Fphrases%20to%20look%20for%20are%20listed.%20You%20can%20replace%20it%20by%20an%20absolute%20reference%20to%20the%20range%20where%20you%20have%20written%20the%20key%20words%20(e.g.%26nbsp%3BSheet2!%24A%241%3A%24A%248).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShould%20you%20have%20two%20key%20words%20in%20the%20same%20text%20(in%20column%20C)%2C%20this%20formula%20will%20return%20only%20the%20first%20key%20word%20found%20from%20the%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183966%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183966%22%20slang%3D%22en-US%22%3EIt%20worked%20nicely!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1185107%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1185107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BWill%20try%20this%20method.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

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")

 

Snag_3e21c3.png

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

 

Highlighted

@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.

Highlighted
It worked nicely!
Highlighted

@Riny_van_Eekelen Will try this method. Thanks.

Related Conversations