How to extract all email ids of a particular company from a given list

[VDS]

@Dear All,

While working with the customer database, I have downloaded email id of of various parties. (attached & change in font colour). Here, one company is having different email ids. For example, from the emal id of hhkook@hyosung.com, Hysong is company name and anything typed before @ is the user. I wan to extract total email ids in the company.

How it can be done.


VDS

 

[Debraj]

In C2.
Use CSE formula as..

=IF(ISERROR(INDEX($B$2:$B$126,SMALL(IF(ISNUMBER(SEARCH(C$1,$B$2:$B$126)),ROW($B$2:$B$126)-1),ROW(A1)))),"",INDEX($B$2:$B$126,SMALL(IF(ISNUMBER(SEARCH(C$1,$B$2:$B$126)),ROW($B$2:$B$126)-1),ROW(A1))))

 

[VDS]

@Debraj,

This is completed & thanks a lot. A small doubt, there are other companies having different mail ids. What I requested is that "Hyosung" is only one company having different mail ids. And there are several companies like that. and want to extract total email ids. Here, company name can be identified after @ sign. say xxxx@abc.com, (abc is the company name).

VDS

 

[Debraj]

Hi VDS..

Did you tried to drag it.. toward Right side..

 

[VDS]

@Debraj,

I got it. It is so superb & classic. Thanks a lot. My confusion was how to match the Column number with Particular Name inside SEARCH function.

Here, a small appreciation to this wonderful forum, I got a better job opportunity with the whole hearted support and assistance from Chandoo. org and would like to benefit from it always. However, may not be able to access regularly due to internet restrictions in the new job that is why reply got it delayed. Please bear with me.

VDS

 

[VDS]

@Debraj,


Still few doubts. I want to colour those different email ids coming under one company name (Column B). How to do this by CF ? Here the excel version is 2010.


VDS

 

[MenofExcel]

You can use conditional formatting. (Highlight cells - contains )

@Debraj, You're awesome.!