How do I extract email addresses from an Excel spreadsheet?

Sometimes, you have data stored in an Excel file that's just too difficult to sort out. Maybe it has multiple columns with headers and all kinds of junk text thrown into one giant cell. Or maybe there is only one field where people entered their name, address, phone number, etc., but no separation between them so now you need to pull each piece of info apart so you can use it elsewhere. Whatever the case may be, here are some ways to get those pesky cells' contents without breaking a sweat.

We'll start by taking two simple examples for starters -- first we will look at extracting email addresses from a single row (or column) of data, then move on to separating addresses from other information contained within the same row or column. We'll take these steps one after another until everything is separated and ready to go!

First up... grabbing individual email addresses from an Excel spreadsheet.

How do I extract addresses from Excel?

This question is pretty straightforward if you've got more than one person entering their own details into the spreadsheet. If not, though, let's break down what happens when you select and copy the whole row or column containing the email addresses. You might think that would work fine, but as anyone who has tried it knows, sometimes it doesn't cut it.

For instance, say you want to grab every email address from a given column, starting at cell A1. In our example below, that means selecting Cells(1,"A") through Cells(7,"A"). Then simply copying the selected range using Ctrl + C. Once you hit enter, you should see all 7 of those rows copied over to your clipboard. Now open Notepad or Word or any application capable of opening XLS files, like Microsoft Office Online. Select File " Open & Export " Other " Comma Separated Values (.csv). This opens up a new window. Enter the following values under Sheet Name: Email Addresses, leave Cell Range blank, check Save as type dropdown menu next to Format, choose CSV, and click Browse. Finally, pick a location to save the.CSV document and click OK. That's all there is to it!

Now whenever you paste the.CSV back into Excel, it won't contain seven copies of the original content anymore. Instead, it will show all entries beginning at the top left-hand corner of Column B, which was previously Row 1. So long as the source spreadsheet had clean formatting, this method should yield accurate results. However, let's try something else.

If you're pulling multiple records from an entire column (instead of just a specific row), you can also take advantage of VBA code to automate the process. The syntax is fairly self explanatory. Here is a sample script showing how to loop through all records in a particular Excel range:

Sub Loop_Through() Dim i As Integer For i = 0 To Rows("50000").End(xlUp).Row Do While Len(Cells(i, 2)) > 0 Set wbkMail = Workbooks.Open _

(ThisWorkbook.Path) With wbkMail.Sheets("EmailAddresses").Rows("49999").Copy Destination:=Range("$B" & i) End With Next i End Sub

You could modify the above script slightly to create an array list of unique email addresses rather than running through every entry individually. Let's put together a short demo demonstrating both methods side-by-side:

In either event, once you've extracted the full list of emails, you can easily isolate and copy whatever fields you wish to keep. Just highlight the desired information, right-click anywhere inside the selection and choose Text to Columns followed by Delimited. From there, remove the commas and retain the actual letters/numbers. Alternatively, you can opt to delete everything except for the email portion.

Either way, make sure you set the delimiter character appropriately based off whatever format the rest of the data takes. Make sure to include tabs or spaces, depending on your personal preference. When finished, head back to Excel and Paste Special - Paste Unique List Only. Your newly created list should appear neatly organized. Repeat this step until you reach the end of the spreadsheet. At this point, you'll have a master list of everyone's email addresses. All that remains is to tidy things up and organize everything properly.

How do I extract email addresses from an Excel column?

Another common task when working with spreadsheets involves isolating individual items from a longer string. Perhaps you want to split someone's address into its various components: home address, mobile phone number, and fax line. Or perhaps you'd like to parse out street numbers and names from a group of houses. There are many situations when it comes time to separate certain pieces of information from others. And luckily, there are several ways to accomplish this feat.

To demonstrate, we will convert three different sets of names, addresses, and telephone numbers into lists of house numbers, street names, and corresponding contact information. First, let's consider a few random houses, listed along with their respective locations, names, and associated phone numbers.

House Number | Street Address | Phone No.

----|-------------------|---------------

123 Main St. | Los Angeles CA 90010

321 W. Elm Ave. | Chicago IL 60610

1337 N. Main St. | Houston TX 77002

1421 S. Rosecrans Ave. | San Diego CA 92106

Let's assume you wanted to write a program that extracts these numerical codes and uses them to determine the corresponding address. Of course, there are plenty of other scenarios where this kind of functionality could come in handy, especially since most spreadsheets don't automatically segregate them into separate groups.

Here's how you can turn that long string of digits into a series of variables representing house numbers, street names, and associated phone numbers:

Select the entire section of data you want to analyze. In this case, we'll focus on the second block of numbers, located in the fourth row of the third column. Highlight the area of interest, press F2, and replace all instances of the word Data with Data(). This command will allow us to refer to any subsequent strings of characters without having to retype the phrase again.

Go ahead and right-click anywhere in the highlighted region, scroll down to Visual Basic Editor, and select Edit Module. Create a New function called SplitString(), naming it accordingly. Inside the brackets, input the following: strSplit(c) Chr(97+Mid(strLine(j),"="c),16)-Chr(65+Right(strLine(j),6)). This part basically says, "Grab every digit between the equals sign and store them in variable c." Replace Mid(strLine(j),"="c) with whichever letter represents whatever numeric sequence you want to capture. In this case, j refers to the current position of the cursor, while Mid lets you specify the middle of said string. Right(strLine(j),6) does essentially the opposite thing: grabs six digits coming before the equal sign. These digits represent the house number. Lastly, Chr(65+Right(strLine(j),6)) converts those numbers into alphabetical form.

Once you run SplitString(), you'll notice that instead of being shown as 1231 South Grand Avenue, the location will instead read House #1231. Hit F8 to execute it again and watch the magic happen!

Repeat this exact procedure for each segment you want to extract. Afterward, fill in the appropriate references for each variable by inserting the correct ranges wherever necessary. Place parentheses around each variable reference, such as [HomeAddress].[StreetName].[PhoneNumber], and voila! Each variable appears to be completely independent of one another. Try pasting the result into Google Maps, and you'll even see how well the output correlates to the physical layout of the homes depicted in the image.

As a final note, it's worth mentioning that splitting strings isn't exactly rocket science. But if you ever find yourself needing to perform these types of tasks regularly throughout your day, you may appreciate learning about built-in functions like Application.WorksheetFunction.Match, which allows you to search for patterns among large blocks of data. Also, because Excel offers extensive programming capabilities via Visual Basic for Applications (VBA), you can develop your own scripts for automating repetitive processes.

How do I separate addresses from pins in Excel?

Just imagine you're trying to chart out an apartment complex. Every building contains dozens upon dozens of rooms, each with their own floor plans. On top of that, each room must be assigned a unique identifier -- much like a postal ZIP Code -- so that you know which box belongs to which unit. Unfortunately, unlike traditional mail sorting systems, Excel doesn't provide a convenient way to handle this issue. Unless you plan to manually assign unique labels to each item, you're going to need to employ some advanced techniques. Luckily, we've already covered a lot of ground today regarding finding duplicate items and converting mixed formats into a proper table. Therefore, we'll skip directly to getting rid of duplicates once we finish separating addresses from pins.

Excel is one of those tools that can be used for so many different things. But, if you're not careful about which tasks it's good at, you might end up wasting time on something that could have been done faster using other software or even by hand.

One such task involves exporting lists of people into separate spreadsheets. In some circumstances, doing this manually requires copying-and-pasting data between two sheets. However, there are shortcuts available for both Windows 10 (using VBA) and Mac OS X users (using AppleScript). We'll show you how to use these methods to export lists of contacts from Excel without having to open another program each time.

For our example, we will create a simple contact list with three columns of information. The first column contains names. The second column has phone numbers. And the third column has email addresses. This makes creating a nice CSV file much easier when sending them over later.

Using either script, you should see all three types of details exported correctly. If you need help setting up any part of the process, let us know in the comments!

How do I copy and paste email addresses from a group in Excel?

The following steps include instructions specific to Microsoft Office 2016 but should work fine for older versions as well.

Open the relevant document containing groups of contacts. For instance, say you want to get all the emails associated with the "Sales" department. You may find yourself working on a table like this:

Select the cell(s) where the email address appears (in this case B2), then right-click and select Copy. Then go to the destination location and hit Ctrl + C to copy. Next, head back to the original source and click Paste. Make sure to check the box next to Formulas before proceeding. When finished, your selection should look like this:

Now that you've copied just one email address, press F5 to refresh the page and view what happens. Your new email address was added to the bottom row. Now repeat the same procedure, selecting more cells until you reach the last cell in the selected section.

When you finish copying the last cell, make sure you change the format of the entire range of cells to Text instead of General. To do this, simply highlight the whole block of text and choose Format Cells... followed by More Settings. Here, uncheck Number tab options, then check Solid Fill option under Font & Border tab. Finally, ensure PlainText under Category dropdown menu is set to Yes. Hit OK once you’re satisfied with your font type.

How do I Export email addresses from a group in Outlook?

If you prefer Outlook rather than Excel, here's a way to quickly move your group of contacts down to a new sheet within the app. First, import your existing excel contacts into outlook. Go to File - Open & Import - Existing Data Files. Browse to the location of your.csv file and then follow the prompts to complete the import. Once completed, open Outlook. Select Tools - Contact List Wizard. From the wizard window, click Advanced Options button. Choose Custom Destination Folder and enter your desired output folder name. Click Create. That's it!

You now have your email addresses imported into a new sheet within Outlook. All that remains is adding a few extra formatting elements. Head to Home tab - Insert tab - Header / Footer… button. Enter additional information including name, company, title, etc., then save changes. With everything properly formatted, you're ready to send your newly created csv file directly to someone via email. Simply navigate to the Output Location field and browse to the correct location.

How do I copy a list of email addresses from Excel?

This method uses VBA programming language. A user form called UserForm1 will handle inputting the necessary fields while also allowing the user to clear existing entries. After everything is entered successfully, a command button named CommandButton3 runs code that copies the values from the Userform1 control array. This technique allows for easy editing because the actual code itself does not require changing.

Step 1: Get familiar with Visual Basic for Applications (VBA): https://www.youtube.com/watch?v=wB_rKZQqFtY&feature=youtu.be

Step 2: Download the sample project provided by the author below: http://anitagames.blogspot.fr/2011/06/copy-paste-multiple-elements-from.html

Step 3: Run VBA editor application

Step 4: Launch Word Document

Step 5: Save current document as macro enabled (.xlsm)

Step 6: Run the Macro

Create a new Excel file. Add a label to identify its contents. Name it whatever suits your purposes.

Enter your email addresses across rows. These must be separated by commas.

Double-clicking on the labels header will automatically insert labels above your inputs. Leave this alone for now.

Right-click on the label you made earlier and select Design View.

Drag a Label object onto the top left corner of the screen. Its size matches the length of the longest entry. Double-click the label. Right-click anywhere inside the label. Select Edit Contents....

A popup window will appear. Type =[email] in the Find Field dialogue box.

Type, in the Search Fields dialogue box.

Click Ok. Close dialog boxes.

Copy and paste the email addresses across cells. Don't worry about separating them yet. Just take note of their locations relative to [email].

Head back to design mode. Drag another Label object underneath the previous one. Set its width to match the height of the shorter emails. Right-click the label and again select Edit Contents.....

In the contents pane, replace "[name]" with the longer email address, e.g., "Mr. Jones". Replace ", Jr." with short version, e.g., "Jr."

Under AutoFormat As, select Comma Separated Values. Check Split string after certain number of characters, then input 7. Press ok.

Close dialog boxes.

Once that's complete, you should run the main routine. Place the cursor near the lower-right edge of the target range. Hold Shift+Ctrl down simultaneously, then release the keys. Selected items will be moved downward, leaving empty space beneath them.

Finally, double-click the uppermost label to activate automatic numbering. Repeat this step for every item in the range.

To maintain consistency, rename all labels to reflect their corresponding entries.

How do I copy and paste multiple email addresses from Excel?

Here's a quick tip for anyone who needs to extract several email addresses from Excel: Instead of inserting individual labels, why not try putting them together in one big chunk? Once you add the labels, the rest of the process is exactly the same as explained in the previous sections.

There are no special commands required to merge ranges of cells, only basic functions. Let's review how to combine a single pair of cells in order to illustrate.

First, start off by importing the data into Excel. Once loaded, select the cells that contain your emails. Highlight the blocks of text you wish to merge and drag them together to consolidate them into one contiguous area. Release the mouse button when you’ve accomplished that goal.

Next, head to the Developer tab and locate the References section. Scroll through the list until you spot Object Library. Expand it by clicking the arrow icon. Within this library, search for Range and select it. Return to the ImportedDataRange variable.

Use Selection.Resize function to resize the merged range so that it encompasses the entirety of your combined cells. Input the appropriate sizing dimensions. Use Selection.Offset function to place the merged range in the proper position. Input offset value equal to 0.0.

Press Alt + Spacebar to launch the Quick Analysis feature. While holding down the shortcut key combination, type rngSelection.Hit Enter.

Your results should display immediately. Notice that the Offset property shows zero. This is due to the fact that the merged range is positioned directly adjacent to the original range. Therefore, nothing gets inserted.

However, if you were to alter the positioning parameter and increase it to 200.00, you would notice that the resulting range shifts upward slightly. This occurs because the original range starts merging halfway.

At this point, you should have enough experience to determine whether combining ranges is worth the effort. Most likely yes, unless you plan on extracting hundreds of addresses. Otherwise, you can always download our free template.

We hope you enjoy our tips and tricks! If you'd like to learn more about transferring data from Excel to Google Sheets, explore our guide.

Excel is great for creating spreadsheets that can be shared with others. If those people use Microsoft Office, then it's easy enough to open up and view such files using their native programs. But what if someone uses Google Docs or Apple Pages instead?

If so, there might come a time when you need to get some information out of one of these documents. Let's say you've created a document full of data about customers, but now you want to move everything over to Gmail rather than just keeping them on paper. Or maybe you have a large workbook containing contact info for dozens of different contacts -- all you really care about right now is getting rid of duplicates. Whatever the case may be, here are a few ways to pull certain types of information off of Excel files.

How do I extract a list of names in Excel?

This is simple, actually! You'll simply select "Data" (or whatever tab contains the column(s) you're interested in), click Data Tools, and choose Text To Columns. This will bring up a window where you can define which columns you'd like to convert into text format. Once you set things up however, you should see something similar to the following screenshot.

Once you click OK, you'll likely notice that not only does each individual cell become its own paragraph, but also any punctuation within that name has been replaced by spaces. For example, let's take a look at my first row. The names are separated by commas, as seen below.

But once they're converted into text, they appear much more cleanly, without any space between words. They read almost exactly the same way as they would if you were reading them straight off of a piece of paper. Just make sure to check the box next to Delimited in Step 2 above before clicking OK.

How do I copy an email list from Excel to Outlook?

It gets even simpler than extracting names! All you have to do after converting the cells via Text-to-Columns is head back down to the Mailings tab. In the Mailings section, you'll find an option called Create Email Message.... Simply follow the prompts until you reach the point where you can specify the source file/workbook and destination folder. Make sure to include the path to the file in question.

Click Save & Send, and you'll receive a message saying that you successfully copied the contents of your Excel file to another program. Now go ahead and tell Outlook to run whenever you wish to send new messages. Head to File " Options / Trust Center Settings, scroll down to General Security Content Type, and under Sender Address Block List add the location of the Excel file. Do likewise for Recipient Addresses Block Lists. When finished, hit Apply and close out.

Now when you launch Outlook, depending on whether you chose New Items Only or Selective Delivery, you should be able to either create brand new mail messages directly from Excel, or import existing ones.

The latter approach isn't ideal because you won't know who sent this particular batch of letters unless you keep track yourself. Thankfully though, you can change this behavior by going to File " Info " Accounts. After doing so, you'll be given access to a dropdown menu labeled Account Types. From here, you can switch the type of account Outlook creates whenever you attempt selective delivery.

For instance, if you don't want to import anything new, uncheck both boxes beneath Automatic Replies. Likewise, if you only want to import new items, uncheck the second box. Finally, if you want to allow all accounts to be imported, ensure that Auto Reply is checked. Note that changing these options will affect every future creation process.

Once you've made changes, save the settings and restart Outlook. Next time you try importing mail messages, you should be prompted to pick between sending new items immediately or selecting specific mailboxes to target.

How do I Import email addresses from Excel into a group in Outlook?

In order to accomplish this task, we must first figure out how many distinct groups exist. We'll assume that our Excel table looks something like this:

To start, highlight the entire thing, and press Ctrl+C to copy it to your clipboard. Then navigate to Home " Paste Special, and click Transpose. This action will turn your rows into columns. As shown below, you should end up with a table looking very similar to the original one except with two extra headers: Name and Contact Information.

At this point, you could manually merge the two tables together into a single larger one. However, since we already have a solution in mind, we'll skip this step. Instead, we'll quickly head over to Insert - Tables. Click Browse, locate the appropriate excel file, and drag it onto the screen. A pop-up window will prompt you to rename the newly added object. Give it a sensible title like Contacts or Customers, whichever makes sense in your situation. Hit Ok.

Next, we'll head back to View - Table Layout. Here, you'll see a preview of your merged table. At the bottom left corner, you can adjust the number of fields per row and per column. By default, it appears as follows:

However, we still haven't figured out how to divide these records into separate categories based upon the content therein. Fortunately, we have a built-in function that allows us to achieve this goal easily. First, select Groups in the Grouping bar, followed by Clear Rules.

Then, head over to Design Mode and select a suitable color palette. From here, you can begin applying colors to various subsets of data. For instance, suppose we wanted to break up our customers into three sections: 1.) Those living inside the United States, 2.) Foreign nationals, 3.) Businesses located outside the U.S..

We'll apply a red background color to the American portion, green to non-Americans, and blue for businesses elsewhere. Begin by heading over to Fill Color, and choosing Red. Next, navigate to Line Border, and double-click on the color wheel icon beside Solid Lines.

Afterward, you'll be presented with several other colors that you can customize. Since we want to differentiate customers residing in America from everyone else, we'll make the border around Americans completely opaque. To do so, double-click on the solid line, and enter 100% transparency in the Transparency field.

When you finish, you should end up with something like this:

By repeating this process across multiple levels of hierarchy, you can assign unique backgrounds to virtually any subset of data. Of course, you can always tweak these borders later on if you decide that you no longer feel comfortable coloring portions of your table in this fashion.

Finally, to complete the job, we'll go back to the main layout page. Right below the header area, insert a Chart titled Customer Distribution. Underneath the chart, place a rectangle anywhere you'd like to display the results. Double-click on the shape and enter a meaningful label in the Shape Style dialogue box.

From here, you can continue tweaking the chart until you're satisfied with the outcome. Be warned, however, that the final version will vary slightly across machines due to differences in operating systems and user preferences.

Can you copy a list of email addresses from Excel into Outlook?

Yes, but it requires some patience. First, highlight your desired range of addresses. Then, head over to Edit - Copy, and paste your selection somewhere safe and permanent. Remember to give the resulting text a descriptive name like Emails. Afterwards, head over to Windows Explorer, and navigate to C:/Users/(your username)/Documents/. Open up OneNote 2016, and head to Insert - Quick Notes.

Here, you'll see a popup asking you to identify the current note. Enter a relevant description underneath the dialog box. Then, tap on Attach Files, and browse through your Documents directory for any useful Excel files. Choose the most recent file, and attach it to your quick note.

With that done, head back over to OneNote. Inside the notes editor, tap Alt + O and select Recent Notes, followed by More Commands - Find Previous. Doing so will reveal a search pane on the far side of your workspace. Use the Search button to scan through your notes, and you should eventually land on yours. Tap on Go and select the attached Excel file.

Afterwards, you can edit the contents of your note as needed, but remember that you cannot delete the attachment itself. Therefore, if you plan on deleting the word "emails," make sure to remove it from the actual document beforehand. Otherwise, you may accidentally erase important information while trying to clean up unnecessary clutter.