# Search and Extract

#### Freedive

##### New Member
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"?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### DonkeyOte

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
Try:

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

#### DonkeyOte

##### MrExcel MVP
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:

Code:
``````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
End Function``````

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

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 = ,

Last edited:

#### jindon

##### MrExcel MVP
UDF
=Freedive(A1,"@vodacom.co.za",",")
Code:
``````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

##### New Member
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

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

#### Freedive

##### New Member
1 address per cell and returned 1 per cell

#### Freedive

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

#### jindon

##### MrExcel MVP
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.

Replies
11
Views
155
Replies
3
Views
226
Replies
0
Views
37
Replies
15
Views
104
Replies
6
Views
46

1,190,873
Messages
5,983,337
Members
439,839
Latest member
iblackie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back