Seperating data

[Tabako1960]

I want to know a formula that will allow me to pull out sections of a string of data in a cell. For instance, when I copy the addresses in an email and paste the results in a cell, they appear like the first cell below and the results I want are like what is the second cell below to the right

"McKintry, Kevin" <K.McKintry@abccorp.com>, "Blass, Bill" <b.blass@transco.com>, "John.Palmer" <johnpalmer@BettleCorp.com>, ...	K.McKintry@abccorp.com, b.blass@transco.com, johnpalmer@BettleCorp.com

 

[rlv01]

With the current version of Excel, a formula solution would not be too much of a problem, but with Excel 2010, your text functions are more limited than with later versions. If you are open to a VBA solution, a user defined fuction (UDF) is an option.

Book1
	A	B
1	Address	Stripped Address
2	"McKintry, Kevin" <K.McKintry@abccorp.com>, "Blass, Bill" <b.blass@transco.com>, "John.Palmer" <johnpalmer@BettleCorp.com>,	K.McKintry@abccorp.com,b.blass@transco.com,johnpalmer@BettleCorp.com
3	K.McKintry@abccorp.com, "Blass, Bill" <b.blass@transco.com>, johnpalmer@BettleCorp.com	K.McKintry@abccorp.com,b.blass@transco.com,johnpalmer@BettleCorp.com
4	K.McKintry@abccorp.com,b.blass@transco.com, johnpalmer@BettleCorp.com	K.McKintry@abccorp.com,b.blass@transco.com,johnpalmer@BettleCorp.com
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=stripAddr(A2)

Something like this will do the job

'UDF to clean email addresses
Function StripAddr(MAddr As String) As String
    Dim S As String, I As Long
    Dim SA As Variant                                 'string array
   
    SA = Split(Replace(Replace(MAddr, "<", ","), ">", ","), ",")
        For I = 0 To UBound(SA)
            If InStr(SA(I), "@") > 1 Then
                S = S & Trim(SA(I)) & ","
            End If
        Next I
        S = Left(S, Len(S) - 1)
    StripAddr = S
End Function

Though there is probably a clever RegEx way to do it as well.