We carry out a lot of work on document and data cleansing, data capture and data blending. One of the most common request is about email addresses and how to automate grabbing just the email address from a string of text in Excel.
Because an email address is a unique entity and also has a unique structure and format, it is quite straightforward to identify an email address but for automation we need to create a function or formula to do this for us where we may have hundreds or even thousands of email addresses in a huge dataset.
Identifiable by its prominent @ in the middle and its characteristics of not being able to have a space in a valid email address, the UDF below works to ‘grab’ the entire email address from the given cell just by using the formula:
…which will return the result of the email address within a string of text in cell A1.
So for instance if the text in A1 was:
‘Here at datanology we understand the importance of clean and accurate data and the value it can hold when managed and used efficiently. Contact us using the contact form below or alternatively drop us a quick email: [email protected] and we’ll get straight back to you with help for your current data situation.”
The result would be:
Like any formula, it can be dragged down to carryout it’s magic against any number of cells in a vertical or horizontal list, or if your feeling confident, it can be used in a VBA loop to run through and find all the email addresses anywhere in the document.
In order to add the UDF to Excel it needs to be pasted into any module in the VBA Editor (ALT + F11) and then it can be used just like any formula you use in Excel.
Feel free to ask us a question, or comment below and we’ll answer your question for everyone to see and learn from.
Function Getmailid(cell As Range) As String
Dim Textstrng As String
Textstrng = cell.Text
[email protected] = InStr(1, Textstrng, "@")
EmStart = InStrRev(Textstrng, " ", [email protected])
If EmStart = 0 Then EmStart = 1
EmEnd = InStr([email protected], Textstrng, " ")
If EmEnd = 0 Then EmEnd = Len(Textstrng) + 1
mailid = Trim(Mid(Textstrng, EmStart, EmEnd - EmStart))
If Right(mailid, 1) = "." Then
Getmailid = Left(mailid, Len(mailid) - 1)
Getmailid = mailid