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

Highlighted

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