How do I extract email addresses from an Excel cell?

We've all received that one spreadsheet. The kind of document where there's just no way for us to know what it contains, and so we have resorted to opening up each individual column with our favorite text editor or word processor. Maybe we're hoping for some sort of pattern, maybe we're looking for certain types of information -- whatever the case may be, if we want to get at any particular piece of data inside a given cell, we need to open up the entire row containing said cell using our preferred method.

But why should we always have to go through the hassle of doing that when Excel can handle it automatically without ever having to touch another app on your computer? Well, Excel is capable of extracting data from cells based upon patterns, which means less time spent on copying-and-pasting columns into new sheets! Let's look at how to use Excel to retrieve email addresses out of a single cell, as well as how to select only part of a cell instead of its entirety. And yes, let's also cover how to extract URLs right out of a cell.

How do I extract an email from a cell in Excel?

If you have a list of contacts saved within an Excel file, then chances are good that those names will appear in a series similar to A1, B1, C1... D10. This makes them easy candidates for extraction by Excel. To see how this works, take the example above, where I entered 4 different entries of my own name as a test subject. If you were curious about how to extract these e-mail addresses from their respective cells, here they are: "johnsmith@gmail.com," "johndoe@yahoo.com," "test1@hotmail.com" and "test2@outlook.com." Now that you understand how to accomplish this task manually, try going back over to the original question posed earlier. Do you think Excel could pull down the same info without having me type anything else?

To make sure Excel has properly extracted the necessary fields out of the four cells listed above, I'll compare it against the results obtained after running a simple VLOOKUP formula across three other rows of the same spreadsheet. Here's the result produced by VLOOKUP: John Smith - Hotmail/Outlook Email Address

As you can tell, Excel was able to successfully identify the first two e-mails, but couldn't quite figure out whether the third entry was meant for Outlook or Hotmail. In contrast, the following image shows the output of formulas like XLOOKUP, HLOOKUP, INDEX/MATCH, ROW() and OFFSET(). As you can see, Excel got everything correct except for the last entry, which appears blank.

So now that you've seen how easily Excel extracts e-mail addresses, you might wonder why anyone would bother entering such details themselves. After all, isn't there something more efficient than clicking away aimlessly between tabs? While Excel cannot replace human intelligence 100%, it does offer the ability to perform various mathematical calculations using conditional formatting rules, statistical functions, etc., thereby cutting down the amount of manual work involved. For instance, you don't have to copy-paste a range of numbers into several separate columns, nor do you have to click multiple times until you find the exact section of code responsible for populating those values. Instead, you simply apply a rule to a group of related numbers and watch as Excel takes care of the rest.

In addition, Excel offers support for complex IF statements that allow users to create dynamic forms of lookup tables. With the help of LOOKUPS, UPs, LIs, ISs, and Sbcs, you can effectively manipulate large sets of numerical data. These commands can even determine the outcome of logical operations depending on the value contained within selected cells. Take a look around on Microsoft Office Online for many examples of advanced formulas used to solve real life problems, including the ones mentioned above.

The next step would probably involve writing a script to automate the process of pulling relevant data from an Excel spreadsheets, but for now we'll stick to learning how to extract e-mail addresses from a single cell. By the end of this article, however, you'll be ready to write a script that pulls data from every cell on a worksheet rather than merely relying on specific criteria. Before moving forward, please note that while Excel allows users to input HTML tags into a cell so that the contents become hyperlinks, this functionality is not available once they've been copied into a regular old text field. So before attempting to embed links into your documents, consider converting them into actual URL strings.

Can Excel extract data from email?

Excel can certainly extract e-mail addresses from plaintext sources, provided that they follow a consistent format. But because most people tend to keep their personal correspondence stored online, you'll likely encounter situations where data formatted in non-HTML formats gets pasted into a cell. When dealing with incoming mail messages, Excel does seem to struggle slightly more compared to when parsing information found in Google Docs or Word files. However, if you happen to receive lots of e-mails that contain attachments, be warned that Excel won't be able to access them unless downloaded via File Explorer. You can avoid this problem altogether by sending yourself PDF copies of important documents whenever possible.

Now that we've established the basics behind how to extract e-mail addresses from Excel, let's move onto the second half of this article, where we explore ways to extract specific pieces of data out of a cell.

How do I extract specific data from a cell in Excel?

After completing the previous exercise, you'd probably realize that Excel doesn't really excel when it comes to extracting certain chunks of data. Even though it managed to correctly recognize four separate e-mail addresses, it still missed the mark when trying to discern between the domains of hotmail vs outlook.com. On top of that, it failed to pick up the phone number associated with john smith's Gmail account. Is there a way for Excel to isolate these kinds of items? Yes, there is! We call it REGEX programming.

Before getting started, we must highlight that although REGEX stands for Regular Expression Matching, it's actually much easier to learn than the term suggests. Essentially, it refers to a set of instructions written in JavaScript designed specifically for finding matches within a string of characters. Once created, REGEXes can be inserted directly into a cell reference for further processing. For example, say you wanted to search for all instances of the letter "A" in the cell containing the e-mail address jane_doe_xyz@domain.com. No matter how often you repeat this action, the resulting match will always produce the same output. Here's how to begin:

Select the desired cell.

Type \a.*@\w+[.]+\w+.

Hit enter.

You'll notice that Excel returns a long line of seemingly random letters and underscores. That's because the character sequence specified in the aforementioned REGEX is fairly general, meaning it accepts almost any combination of characters. Although the phrase.* seems scary at first glance, it basically serves as a wildcard that lets the program scan through each subsequent element for potential matches. In reality, however, it plays absolutely zero role in determining whether a matching pair exists. What's more, the asterisk symbol (.?) is usually reserved for indicating optional elements that aren't required for a successful match.

When creating regular expressions, you'll typically start off by searching for something very basic, such as *, +, {0,} or [^]. Next, proceed to add additional modifiers accordingly, adjusting the length of the sequence allowed to occur. Modifiers include things like?, $, ^, |, and (among others). Finally, wrap your expression in parentheses to provide proper context for the remainder of the statement.

For example, suppose you wanted to locate all words composed of exactly six lowercase alphabetic characters. Your initial approach would be to specify the regex shown below, followed by pressing Ctrl+Shift+Enter to run the query. But since this particular command matches any 6-letter substring regardless of case sensitivity, we'll modify it to reflect what we're aiming for. Try changing the final modifier to i to indicate that the search should return upper- and lowercase versions alike.

Once you've got the hang of basic REGEXes, it becomes relatively straightforward to expand your knowledge base by adding qualifiers and exclusions. For example, let's assume you want to grab the city associated with someone's home address. One popular solution involves using a parenthesis statement to exclude cities whose abbreviations differ from place to place. To achieve this goal, you can insert a negative sign (-) immediately following a location abbreviation to eliminate matches involving things like NY, Boston, Chicago, et cetera.

Here's a sample REGEX that accomplishes both tasks: City:(?!NY|Boston|Chicago)|(?:New York|Brooklyn|Manhattan), State:/((CT|MA)?|NH).

A common scenario for many users is that they have thousands of contacts saved as emails. To manage them, it's often easier just copy and paste each address into a separate text field or column. But what if you want to organize these people by their name instead of their email address? Or maybe you need to find all the emails sent out by someone within a certain department at work -- can you use VLOOKUP to pull those names down too? While there are plenty of ways to merge cells together in spreadsheets, extracting email addresses from a single cell isn't so easy... unless you're using VBA!  Let me show you how to get started with some examples on how to extract email addresses from one cell.

How do I extract email addresses from a hyperlink in Excel?

To create a new spreadsheet, go File " New. Then select Blank Workbook. This opens up a blank document where we'll be working our magic. In my example above, I've created two sheets called Sheet1 and Sheet2 and placed three different links in Sheet1. When clicked, clicking any link will open up a web browser window displaying information about said company. The problem lies when trying to extract all the email addresses contained in Sheet1. You could try copying down every email manually but who wants to do THAT?! Instead, let's take advantage of Excel formulas to grab everything automatically.

First thing we must do is create a formula which extracts the email address(es). We can accomplish this easily enough by combining several functions. First, select the cell containing the hyperlinks. For example, here's the first link ("Company A") selected:

Then right-click anywhere on the row under that selection, then choose Insert Function. Now type =HYPER() in the box labeled Name. Finally, click Add (+), scroll through the list until you see HYPER(), and highlight it. Click OK. Your screen should look something like this now:

Now drag over another empty row beneath the previous one. Do the same process again -- insert function=HYPER(). Once you hit Add (+), pick HYPELEMENT. Again, make sure you select the last entry and not anything else. Hit OK. Repeat that step for both rows until you reach the bottom of the data set. By doing it this way, you don't have to worry about accidentally adding other functions, because none exist yet. Here's what I ended up with after dragging out two additional rows:

As you can see, the formula has extracted all the unique elements found within Column B. However, since this was done with manual input rather than code, those extra entries were left untouched. If you wanted to exclude those results, simply add TRUE to the end of the statement: =HYPER("a",TRUE). As long as you leave FALSE alone, however, you'll still capture those unwanted strings. Next, we'll combine the two parts of the formula into one, complete command. Select all cells between the second and third rows, including the ones you added afterward. Right-click somewhere inside the group, then select Edit Functions.... From the dropdown menu, select AND.

Finally, press OK. What you should receive is a string representing all possible values in Column B. Copy this entire block of code and put it directly underneath the original value you chose earlier. So for instance, I copied the following line of code:

And pasted it next to the original Cell D4:

Once you've got it positioned properly, the final result looks like this:

That's exactly what we needed -- the ability to extract whatever comes after the @ symbol from a hyperlinked cell. At least, assuming the person didn't change the default behavior. Let's say we want to modify this script to allow us to extract more than just the first part of the e-mail address. Like, if you had a user named John Smith whose email looked like smith@companyx.com, we'd also like to include johnsmith@companyx.com into the equation. That means we'll need to adjust the logic slightly.

Select the whole section of cells once more and head back to the Editing Functions page. Scroll down until you spot CONCATENATE and highlight it. Choose ADD from the options provided. Once you do, delete the existing contents of the resulting box. Paste the new piece of code you created before, but replace CONCATENATE with SUBSTITUTE. Replace CONCATENATE with SUBSTITUTE to remove the commas. Make sure to keep the rest of the syntax the same. Here's what you should have:

The difference being that CONCATENATE removes the spaces while SUBTUTUTE keeps them intact. Just remember that whenever you substitute a function, you MUST ALWAYS PASTE THE EXACT SAME CODE AS IT APPEARS IN YOUR SCREENSHOTS!! Otherwise, you might wind up mixing up your old commands. Save the file and close it without changing anything else. Run the macro and check out the results:

So far, we've managed to isolate the first part of the email address, meaning we can strip off the domain name and suffix to reveal the actual recipient's real email. Great job! Not bad for requiring less than 10 minutes worth of coding time either. Onward!

Next, we'll learn how to extract multiple email addresses from one cell. After creating a couple more rows, this time with hyperlinks, enter the exact same bit of code as before. Only this time, though, we're going to tweak the IF statements a little. Since the goal is to extract multiple e-mails, we'll break up the CONCATENATE/SUBSTITUTE combo into smaller chunks. Try this:

This particular method allows you to extract up to four distinct e-mail addresses from a single cell. Of course, feel free to play around with it and come up with your own ideas -- perhaps even incorporating a wildcard variable like %MAILER%. Also, if you decide to skip ahead to the next section, please refer back to the screenshot above to ensure you haven't modified any settings.

In addition to separating the SUBSTITUTE command into segments, you may notice that we also introduced a + character into the mix. Whenever you introduce variables into your string calculations, you always need to precede them with a plus sign. Why? Because otherwise, Excel won't know what you mean. Don't ask me why, I just read the documentation and learned it. Anyway, here's the full version of that code:

Here's what happens when you run it:

You should understand how powerful this technique can become. All you really need to do is identify specific patterns within your data set and build upon them. Even better, if you happen to have a lot of columns containing similar sequences of characters, you can actually perform similar operations across multiple ranges of cells simultaneously. For instance, let's say you had a log book filled with employee ID numbers. Every time an employee checked in, his number would appear in the appropriate location. You could write a simple VB script to scan through the entire book and print out all employees' IDs along with their corresponding names. Easy peasy!

Q. How can I get a list of email addresses out of a column full of names and other data? For instance, if my spreadsheet has a "Contact" section that contains first name, last name, phone number, street address, city, state/province, zip code, home address, mobile number, email address, etc., what kind of formula would be best for extracting just those email addresses into another field?

A. You have two options -- you could use VBA or a custom function to pull them out one at a time. If you're looking to automate tasks with macros, there's no need to manually copy-and-paste each one, as it will take too long!

Let's start with some examples so we know exactly which cells contain email addresses. Then we'll move on to creating formulas that grab only those specific fields.

For our example, let's say you want to create a list of all contacts who live within 100 miles of where you work. This is easy enough to accomplish using conditional formatting. In Column A, highlight all rows containing info about people living within 100 miles of your workplace. Next, in Cell B2, type =iferror(vlookup("address",$B$1:$C$100,3),0). The vlookup() method searches through the entire range specified ($B$1:$C$100, 3) for any values matching the value located in D2, then returns the corresponding cell when found. So now, any entries that don't match the search criteria must return 0, meaning they fall outside of the area searched. Finally, set up the rule described above to apply to this new cell. When you click Format & Conditional Styles... choose Red Fill.... Now, whenever you enter more than 100 miles away from work, these cells will turn red.

So far, we've highlighted certain columns that contain unique information. But how do we go about isolating just the email addresses from a larger chunk of text? Let's look at several methods.

How do I separate emails by commas in Excel?

First off, let's make sure that every entry consists of three parts before moving onto solutions. We'll assume that everything else stays the same. Here’s what your data should look like after copying over all the data from the original spreadsheet:

If you notice something wrong, feel free to run down this checklist yourself. Otherwise, skip ahead to the next step. Before proceeding, however, ensure that none of the following conditions exist: duplicate contact records, empty strings, extra spaces between words, lines longer than 70 characters, etc.

Next, open up your Excel file and paste the contents of the EMAILS_LIST.xlsx tab into a fresh document. At the top left corner of the window, under Ribbon " Developer, scroll down until you see the Design Mode button. Click the icon. Under Visual Basic Editor, double-click Sheet1 to access its default subroutine. From here, add the following line of code: Dim strEmail As String Dim intStartPos As Integer Dim intEndPos As Integer Set oExcel = CreateObject("excelfile").Open """"""EMAIL_FILE"""".txt"""""" intStartPos= 1 intEndPos = Len(strLine) '' Loop While intStartPos "" 50000 And intEndPos "" 70000 Do MsgBox ("Extracted Email Address:"& Mid(strLine,intStartPos,intEndpos - intStartPos)) intStartPos = intEndPos intEndPos = Len(strLine, intStartPos) ContinueLoop End With

This code uses the lenght property to determine the length of the string variable strLine, which stores the current row being processed. Once the loop begins, it checks to see if the end position falls beyond the limit (70000), but not once does it check to see if the beginning position exceeds 5000. This means that the loop won't stop unless someone enters more than 5000 characters. This also prevents us from accidentally pulling anything past the limit.

Once the loop executes, a message box displays whenever the script detects an extracted email address. However, since we haven't yet figured out how to transfer the result back into your main spreadsheet, nothing actually gets sent anywhere. To fix this problem, insert the following line of code right beneath the previous one: ActiveWindow.Selection.Copy _'COPY THE STRING THAT YOU JUST DETECTED INTO ANOTHER CELL

Now, instead of displaying a message box, the script copies whatever was detected into another location. Since the outputted text was stored as active content, we had to tell excel to “copy” the selection rather than simply display it. The second command tells excel to put the selected text into the clipboard, allowing us to manipulate it later without having to worry about changing formats during transfers.

You may wish to change the variables used above to suit your own needs. Feel free to adjust the parameters for the while statement, too. Also note that you cannot modify existing codes already present in Microsoft Word files. That said, you can still write scripts using the macro recorder feature available in Office 2016. After running your newly created routine via design mode, try modifying the code itself to test things out.

How do I isolate email addresses in Excel?

The process is pretty similar to the above solution, except that this time around, we’ll combine the detection logic and extraction functionality together. First, place a breakpoint on the line that reads msgbox(""), then hit F5 to debug the program further. Make sure that the word “msgbox” isn’t grayed out. If it is, hold Shift + Ctrl + Alt while pressing F5 to force debugging. Afterward, input the following line of code directly underneath the break point: ActiveWindow.Selection.Copy _'COPY YOUR SELLECTION TO CLIPBOARD AND THEN USE REPLACE FIELD FORMULAS FOR EXTRACTING CONTENT FROM CELLS IN COLUMNS WITH ONLY EMPLOYEE ID NUMBER.

Here, we’re combining both functions. We’ve placed the msgbox call inside the loop so that it runs repeatedly until the user presses Enter. What happens here is that the script looks through the whole dataset and scans for instances where the input is greater than 7000. Once it finds such occurrences, it extracts the email addresses contained therein and puts them in the Clipboard.

To finish off the procedure, head over to the Contact Information tab and select All Contacts. Highlight the appropriate record and press CTRL+V to Paste Special.. Select Transpose. Drag the resulting table across the columns labeled Job Title, Department, City, State, Postal Code, Home Phone, Mobile Phone, Primary Email, Secondary Emails, Other Info, Notes, and Additional Comments. Notice that the secondary emails were automatically transformed into hyperlinks. These links lead users straight to their respective inboxes.

Afterwards, delete unnecessary rows. Then close the document and save changes. Copy the final version of the file and rename it appropriately. Whenever you receive messages through Gmail, Outlook, Yahoo!, Hotmail, or AOL, remember to refer to the sender by typing his or her email address followed by @[insert domain].

How do I select all email addresses in Excel?

In order to include multiple email addresses per employee, we’ll need to perform two operations. First, we’ll use the Selection object to detect whether an individual exists in the data set. Second, we’ll use the FindAndReplace Object tool to replace all blank space with underscores.

Place a breakpoint on the msgbox line and execute the program again. Hit F5 to debug. Input the following line immediately afterwards: ActiveWindow.Selection.FindAll "[firstname].[lastname]", [what:is], ReplacementFormatting: True, MatchCase: False, SearchOrder: xlByRows, LookAt:=xlWhole, ReplaceWith:="_"

This sets up the proper parameters for finding all instances of either the individual’s first or last name. Afterwards, input the following line of code: ActiveWindow.Selection.FindAndReplace Where:=wdSearchTextNotFound, Text:="_", replacementformatting:true, flags:=-4163441893, format:=""

Using wdSearchTextNotFound causes the FindAndReplace tool to ignore matches entirely. By setting the Flags parameter to -4163441893, we instruct Excel to consider the results returned but ignore them completely. Additionally, we pass the flag -4163441892 so that Excel doesn’t attempt to change any of the original data. Lastly, set the format argument equal to an empty string because we don’t want any replacements made.

Again, you can alter the settings listed above depending on your preferences. Save changes and continue editing. Head over to the Contact Information tab and locate the desired employee. Right-click on the relevant cell and select Edit Formulas.... Choose Criteria Options... and select List item from the dropdown menu. Press OK and watch the magic happen.