Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Sorry if the questions is not very explanatory. Basically I have a large sheet of data on prospect clients. The data includes their email addresses, however they're sparse and not in set locations (see below). I would like create a function that would search for phrases that contain word "email" and add them to a new column.

During the process if the function could strip the phrase from the word "email:" that would be fantastic.

Please help!

Example of my data:

enter image description here

share|improve this question
Are all the email addresses in the same column? – CharlieRB May 16 '12 at 11:57
I would suggest doing some manual preparation before hand to have the data, less sparse and more organized. If you can, try to get all of the email addresses to the same column. Then use Data Validation > Text to Columns, with : as the delimiter. It will return only the email addresses without the prefix email:. – rishimaharaj May 21 '12 at 12:10

I think simply you can use Ctrl+F to keyword search for the specific word like "email".

share|improve this answer
That is a great solution for a simple search, but they are looking for a function to extract the data to a new column, as well as remove the word email. – CharlieRB May 16 '12 at 11:53

VBA is the way to go in this scenario. I've pulled together some code that you can use to search through a sheet for email addresses (preceded by "email:"). It will return the email addresses in a column on a new sheet named "Email List". Just paste the following into a module and run the Sub GatherEmails.

Option Explicit
Sub GatherEmails()
Dim s1 As Worksheet, r1 As Range, s2 As Worksheet, r2 As Range
Dim tmp() As Variant, rws As Long, cols As Long, tmpStr As String
Dim tmpOut As String, output() As String
Set s1 = ActiveSheet
Set r1 = s1.UsedRange
'Load all data from sheet into array for fast processing.
tmp = r1.Value
'Loops through all values, extracts email addresses, and stores them in one space-delimited string.
For rws = 1 To UBound(tmp, 1)
    For cols = 1 To UBound(tmp, 2)
        If tmp(rws, cols) <> "" Then
            tmpStr = MatchEmails(CStr(tmp(rws, cols)))
            If tmpStr <> "" Then
                tmpOut = tmpOut & tmpStr & " "
            End If
        End If
    Next cols
Next rws
Erase tmp
'Remove trailing space from string
tmpOut = Left(tmpOut, Len(tmpOut) - 1)
'Store email addresses in an array.
output = Split(tmpOut, " ")
'Create new sheet and print array of email addresses there.
Set s2 = Sheets.Add
s2.Name = "Email List"
Set r2 = s2.Range("A1").Resize(UBound(output) + 1, 1)
r2.Value = Application.WorksheetFunction.Transpose(output)
Erase output
End Sub

Private Function MatchEmails(searchstring As String) As String
'Uses regex pattern to find email addresses preceded by "email:" and strips away "email:".
'Returns all matches in one space-delimited string.
Dim objRegex As Object, matches As Variant, i As Long
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "email:([^ ]*)"
    Set matches = .Execute(searchstring)
If matches.Count > 0 Then
    For i = 1 To matches.Count
        MatchEmails = MatchEmails & .Replace(matches(i - 1).Value, "$1") & " "
    Next i
    MatchEmails = Left(MatchEmails, Len(MatchEmails) - 1)
    MatchEmails = ""
End If
End With
End Function

Note: You will have to add a reference to use the regular expression syntax in the function. In the VBA editor, go to 'Tools' >> 'References...', then check the box next to 'Microsoft VBScript Regular Expressions 5.5'.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.