The Smarter Way to Extract Email Addresses in Excel

Once you have used Excel for a while you start to push the limits of what the built-in functionality can do. This is not as bad news as it might at first appear because part of the power of Excel is the fact that you can build on this base feature set by creating your own code in Visual Basic!

You have essentially two main ways anyone can easily extend Excel and the first, Macros, are pretty familiar to most Excel users. The second though seems to get overlooked, and this is User Defined Functions.

The part I love about User Defined Functions is that you can use them right from your formula, and another cool aspect is you can reuse these functions, so over time you start to build up a library of useful functions as you get more experienced.

Building an Example UDF

To demonstrate the kind of function I am talking about, let’s build a function that extracts an email address from a piece of text. The email address could appear anywhere in the block of text, so we need to discover it by looking for the @ symbol.

Before you start working with User Defined Functions, make sure you have your developer ribbon enabled in the options:

Enabling the developer features allows you to go into the Visual Basic editor and start creating your function.

The “Extract Email from String” Function

Here is our function. You will see it begins with the line describing the function, with the function name and the variable type that will be passed to it and returned from it after execution. Essentially we can pass most Excel data to this function, it will look for the Email address, and return it for us to use.

We start by taking apart the original text by splitting it into an array based on where semicolons appear. This could be easily changed to comma as the delimiter, or even changed to have this passed as an argument.

Next we go through each array element looking for the @ symbol. If we find it then the whole array element is returned as we exit the function.

Here is the function in action!

Summary

Of course User Defined Functions can do a lot more impressive things than look for an email address! This simple example though I hope demonstrated all the basics you need to know in order to start working with your own User Defined Functions. We created a Function that accepted a parameter, worked on some data and returned a value – pretty much what you will need to know every time you come across something you wish Excel did already!

P.S.

Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time and improve your productivity.