Look Who it Was Random video

Excel - Extract all E-Mails Ending in .edu to a new worksheet - Episode 1006

345 views · Published 4 May 2009 · 2:23 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Extract all E-Mails Ending in .edu to a new worksheet.

Welcome back to the MrExcel netcast! In this episode, we're tackling a common problem faced by many Excel users - how to quickly sort through a massive amount of data and extract specific information. Specifically, we'll be looking at how to filter out email addresses that end in .edu and move them to a separate sheet.

This question was sent in by Eileen, who has a worksheet with 20,000 email addresses and needs to find all the ones that end in .edu. So, how do we go about solving this problem? Well, the first step is to insert a few new rows and create a Criteria Range. This range will have the heading of your email addresses and the criteria we're looking for, which in this case is *.edu.

Next, we'll use the Advanced Filter function. In older versions of Excel, this can be found under Data ＞ Filter ＞ Advanced Filter. In the newer versions, it's simply Data ＞ Advanced. We'll select the option to filter the list in-place and copy it to another location. The Criteria Range will be the two cells we just created, and the Copy to location can be a blank space on the same worksheet or a separate one.

Once we click OK, we'll see that all the email addresses that end in .edu have been filtered out. But what if we also need to find email addresses that end in .gov? No problem! We can simply add another criteria to our Criteria Range, in this case, *.gov. This will give us all the email addresses that match either criteria.

The Advanced Filter function is a great tool for quickly sorting through large amounts of data and extracting specific information. However, keep in mind that it will only filter the data in-place, so you'll need to copy and paste it to a new sheet if you want to keep the filtered results. Thanks to Eileen for sending in this question and thank you for tuning in to another netcast from MrExcel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


leane asks how to get only the .edu e-mail addresses from a database of 20,000 e-mails. Episode 1006 shows you how to use advanced filter with a criteria range to solve this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction 
(00:14) Solving a Problem with Advanced Filter 
(01:10) Using Advanced Filter for Multiple Criteria 
(01:40) Advanced Filter for Sorting Data 
(01:50) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152393/

More from this channel