copy paste

[oracle_coorg]

hi
in COLUMN B i have some records or row value which are closed <>
eg: "ddddaaaa" <aaaa@gmail.com>
i need to get only the mail id that is the value inside the closed coat's eg: aaaa@gmail.com
in same sheet in column D
attached the .xls

[rory]

You can use this formula assuming the original value is in A1:
=SUBSTITUTE(MID(A1,FIND("<",A1)+1,LEN(A1)),">","")

[oracle_coorg]

hi rory how r u ... thanxs for the last post
ok
i need to get each value (email) in seprate row eg in the attachement
can i get a macro out for it

[rory]

Oh I see, you have multiple emails in one cell. Do you want each email in a separate column on the same row, or just one long column with all the emails in it?

[oracle_coorg]

ya u r right i need all the email in one single column
i have email in cell b with three rows
i want all the email in one single column
hope the sample .xls will give an idea ..

[rory]

You can use this (uncomment the Sort line if you don't want gaps in between the groups):
Sub GetEmails()
Dim rngCell As Range
Dim lngRow As Long, lngRowCount As Long
Dim varData, varItem
lngRow = 2
For Each rngCell In Range("B2", Cells(Rows.Count, "B").End(xlUp))
varData = Split(rngCell.Value, ",")
lngRowCount = UBound(varData) + 1
Range(Cells(lngRow, "D"), Cells(lngRow + lngRowCount - 1, "D")).Value = Application.Transpose(varData)
lngRow = lngRow + lngRowCount
Next rngCell
Set rngCell = Range("D2:D" & lngRow)
With rngCell
' .Sort key1:=.Cells(1, 1), header:=xlNo
.Replace "*<", "", xlPart
.Replace ">", "", xlPart
End With
End Sub

[oracle_coorg]

hi
rory ur simple gr8
:bow:

[rory]

Yeah, my wife thinks it's great that I'm simple, too. :)