I had 6,000 email ids in one column.
I would like to have separate column for each unique id for ex. yahoo,
hotmail etc. From Find and Replace I gave Yahoo and choose Find All options.
In results I find all yahoo entries alone.
How can I transfer those yahoo entries alone and insert in the excel sheet
as separate column?
--
K. Mahendra Raj, Coimbatore

Assuming that A4:A100 contains your data, try the following...

Let C2 contain Hotmail

Let D2 contain Yahoo

C3, copied across to D3:

=COUNTIF($A$4:$A$100,"*"&C2&"*")

C4, copied down and across:

=IF(ROWS(C$4:C4)<=C$3,INDEX($A$4:$A$100,SMALL(IF(I SNUMBER(SEARCH(C$2,$A$4
:$A$100)),ROW($A$4:$A$100)-ROW($A$4)+1),ROWS(C$4:C4))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Mahendra raj, Coimbatore
wrote:

I had 6,000 email ids in one column.
I would like to have separate column for each unique id for ex. yahoo,
hotmail etc. From Find and Replace I gave Yahoo and choose Find All options.
In results I find all yahoo entries alone.
How can I transfer those yahoo entries alone and insert in the excel sheet
as separate column?

On May 8, 1:12 pm, Mahendra raj, Coimbatore
wrote:
I had 6,000 email ids in one column.
I would like to have separate column for each unique id for ex. yahoo,
hotmail etc. From Find and Replace I gave Yahoo and choose Find All options.
In results I find all yahoo entries alone.
How can I transfer those yahoo entries alone and insert in the excel sheet
as separate column?
--
K. Mahendra Raj, Coimbatore

Assuming your emails are in A2:A6000, you can place the following
formula in, say, F2:

=INDEX($A$2:$A$6000,SMALL(IF(ISNUMBER(SEARCH("@yah oo.",$A$2:$A
$6000)),ROW($A$2:$A$6000)-ROW($D$2)+1)))

This is an *array* formula, commit with Shift+Ctrl+ENter. Copy down
until you get error values. If needed, you can then copy the new
column and Edit|Paste Special...Values

HTH
Kostis Vezerides