Find & Select help needed - please see image

%3CLINGO-SUB%20id%3D%22lingo-sub-1500954%22%20slang%3D%22en-US%22%3EFind%20%26amp%3B%20Select%20help%20needed%20-%20please%20see%20image%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500954%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20hope%20everyone%20is%20well.%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20contains%20Firstname%2C%20Lastname%2C%20Email%20Address.%3C%2FP%3E%3CP%3EI%20use%20Find%20%26amp%3B%20Select%20to%20search%20for%20all%20the%26nbsp%3B%40gmail.com%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20how%20do%20I%20expand%20to%20also%20include%20Firstname%20%26amp%3B%20Lastname%20associated%20with%20that%20email%3F%20to%20then%20copy%20and%20paste%20in%20to%20a%20new%20csv%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1500954%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1501078%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20%26amp%3B%20Select%20help%20needed%20-%20please%20see%20image%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1501078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F715292%22%20target%3D%22_blank%22%3E%40discount85%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20find%20%2F%20select%2C%20why%20not%20add%20a%20filter%2C%20filter%20on%20the%20email%20address%20column%20for%20entries%20containing%20or%20ending%20with%26nbsp%3B%40gmail.com%20then%20that%20would%20allow%20you%20to%20copy%20%2F%20paste%20only%20the%20filtered%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1503748%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20%26amp%3B%20Select%20help%20needed%20-%20please%20see%20image%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F715292%22%20target%3D%22_blank%22%3E%40discount85%3C%2FA%3E%26nbsp%3BHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20attached%20screenshot%20%221.JPG%22.%20Hope%20it%20is%20in%20the%20expected%20format%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EFormula%20used%20in%20the%20cell%20D2%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(SEARCH(%22gmail%22%2CC2))%2CCONCAT(A2%2C%22%20%22%2CB2)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20search%20for%20the%20keyword%20gmail%20in%20the%20email%20column%20and%20if%20true%2C%20will%20combine%20the%20contents%20of%20the%20cell%20A2%20%26amp%3B%20B2%20along%20with%20a%20space%20in%20middle.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20copy%20this%20in%20new%20csv%2C%20simply%20open%20a%20new%20csv%20and%20instead%20of%20copying%20the%20formula%2C%20type%20it%20and%20after%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3ESEARCH(%22gmail%22%2C%3C%2FEM%3E%3C%2FFONT%3E%26nbsp%3B%20switch%20to%20old%20csv%20(alt%2Btab)%20and%20click%20on%20the%201st%20cell%20in%20the%20column%20email.%20Now%20again%20alt%2Btab%20to%20go%20to%20the%20new%20csv%20and%20further%20type%20two%20closing%20brackets.%20Now%2C%20type%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3ECONCAT(%3C%2FEM%3E%26nbsp%3B%3CFONT%20color%3D%22%23000000%22%3Eand%20alt%2Btab%20to%20old%20csv%20and%20click%20on%20A2%2C%20the%20alt%2Btab%20to%20new%20csv%20and%20type%20%3CFONT%20color%3D%22%23FF0000%22%3E%2C%22%20%22%2C%3C%2FFONT%3E.%20Now%20do%20the%20same%20for%20the%20rest.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, hope everyone is well.

I have a spreadsheet that contains Firstname, Lastname, Email Address.

I use Find & Select to search for all the @gmail.com 

But how do I expand to also include Firstname & Lastname associated with that email? to then copy and paste in to a new csv

 

Thanks 

2 Replies

@discount85 

 

Instead of find / select, why not add a filter, filter on the email address column for entries containing or ending with @gmail.com then that would allow you to copy / paste only the filtered list.

@discount85 Hi

 

See the attached screenshot "1.JPG". Hope it is in the expected format

Formula used in the cell D2 is:

 

=IF(ISNUMBER(SEARCH("gmail",C2)),CONCAT(A2," ",B2),"")

 

This will search for the keyword gmail in the email column and if true, will combine the contents of the cell A2 & B2 along with a space in middle.

 

To copy this in new csv, simply open a new csv and instead of copying the formula, type it and after SEARCH("gmail",  switch to old csv (alt+tab) and click on the 1st cell in the column email. Now again alt+tab to go to the new csv and further type two closing brackets. Now, type CONCAT( and alt+tab to old csv and click on A2, the alt+tab to new csv and type ," ",. Now do the same for the rest.