Search and Extract

[Freedive]

I have a list of email addresses in column A (eg: andre@mailburst.co.za , pete@ vodacom.co.za). Is there a way to ectract say only the addressesending in "@vodacom.co.za"?

 

[DonkeyOte]

can you elaborate on the following:

-- are the addresses in one long string in one cell in column A or in multiple cells (1 address per cell) ?

-- are there going to be multiple addresses meeting the criteria and if so how are they to be returned (in one long string or one address per cell etc...)

 

[Jon von der Heyden]

Try:

=RIGHT(A1,LEN(A1)+1-FIND("@",A1))

 

[DonkeyOte]

if you want to return all addresses containing "vodacom.co.za" (wherever they may appear in the string of addresses) to one cell you could use a function:

Function mail_addresses(str As String, crit As String, delim as String)
Dim cnt As Integer: cnt = (Len(str) - Len(Replace(str, crit, ""))) / Len(crit)
Dim ans As String: ans = ""
Dim i As Integer
Dim pos_1 As Long
Dim chr_i As Long
Select Case cnt
    Case 0
        ans = ""
    Case Else
        For i = 1 To cnt
            ans = crit & delim & ans
            pos_1 = InStr(str, crit)
            For chr_i = pos_1 - 1 To 0 Step -1
                If Mid(str, chr_i, 1) = delim Then Exit For
                ans = Mid(str, chr_i, 1) & ans
            Next chr_i
            str = Trim(Mid(str, pos_1 + Len(crit), 10000))
        Next i
End Select
mail_addresses = ans
End Function

Say your addresses were in a string in A1, in B1 you could enter

=MAIL_ADDRESSES(A1,"vodacom.co.za",",")

where vodacom.co.za is your ISP identifier and comma is your address delimiter (ie character separating addresses in your string -- could be ";" in some cases).

If you wanted to store the criteria and delimiter in cells you could simply refer to those cells instead - ie MAIL_ADDRESS(A1,B2,C2) where B2 = vodacom.co.za, C2 = ,

 

[jindon]

UDF
=Freedive(A1,"@vodacom.co.za",",")

Function Freedive(txt As String, mySearch As String, _
                  delim As String) As String
Dim e
For Each e In Split(txt, delim)
    If e Like "*" & mySearch Then _
        Freedive = Freedive & IIf(Freedive<>"",",","") & Trim(e)
Next
End Function

 

[Freedive]

can you elaborate on the following:

-- are the addresses in one long string in one cell in column A or in multiple cells (1 address per cell) ?

-- are there going to be multiple addresses meeting the criteria and if so how are they to be returned (in one long string or one address per cell etc...)

1 address per cell and returned 1 per cell

 

[DonkeyOte]

Jindon, once again putting me to shame... very nice code... I always forget Split!

 

[Freedive]

1 address per cell and returned 1 per cell

 

[Freedive]

jindon ... I'm a bit of a newbie to all this ... how would I enter the code?

 

[jindon]

jindon ... I'm a bit of a newbie to all this ... how would I enter the code?

1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again
Use in the cell like
=Freedive(A1,"@vodacom.co.za",",")
It returns myltiple result joined by comma "," when A1 holds multiple address to be extracted.