Extracting Email Addresses from Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we learned in detail about extracting email addresses from Excel including Flash fill, Text to Column feature, Excel formulas and VBA code.

Extracting email addresses is beneficial when working with a large dataset and you need to separate the email addresses for further work. With extracting email addresses, you can streamline communication efforts and send bulk emails for various purposes.

In the following, you will find an overview of extracting email addresses from Excel.

extracting email addresses from Excel


How to Extract Email Addresses from Excel: 4 Ways

In the following, I have shared 4 simple ways to extract email addresses from Excel. Suppose we have a dataset containing Employee Name, Email Address, and Contact Number. Now we will extract email addresses using Excel tools, built-in functions, and formulas.


1. Extracting Email Addresses Using Excel Tools

We can do it following two ways-


1.1 Using Flash Fill

Flash fill is a wonderful feature that can be incredibly helpful when it comes to extracting email addresses from a column of data. It uses pattern recognition and intelligent data analysis algorithms to automatically fill data based on patterns it identifies.

  • First, type your desired content to the next side-by-side column. Here, I wrote the email address in cell (C5).

Writing email address manually in a single cell to flash fill

  • Second, selecting cells (C5:C12)) click Flash Fill from the Home ribbon.

Selecting flash fill feature from the Home ribbon

  • Within a glimpse of an eye, you will get your desired result by extracting email addresses from the table. Simple isn’t it?

Final result with extracting email addresses using flash fill feature


1.2 Utilizing Text to Columns Feature

When working with Excel spreadsheets, extracting specific information, such as email addresses, from a column of data can be a common requirement. Luckily, Excel provides a versatile feature called Text to Columns that allows you to split text into separate columns based on specified criteria.

  • To start with, choose the table from which you want to extract email addresses.
  • Next, click Text to Columns from the Data tab.

Selecting Text to Columns feature from the Data tab

  • From the Convert Text to Columns Wizard window press Next.

Checkmarking Delimited option and hitting Next button

  • In the next step, put a comma (,) into the Others section and click Next.

Inputing delimiters in the Other tab and pressing Next

  • Hence, choose your destination cell ($C$5), checkmark the Do not import column option to skip multiple columns and press Finish.

Selecting destination cell and clicking Finish

  • Finally, we have successfully extracted email addresses from Excel. It’s that simple.

Final output with extracting email addresses using text to column feature

Read More: How to Make an Address Book in Excel


2. Extracting Email Addresses from a Single Column

We can do it following three ways-


2.1 Utilizing TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SEARCH Functions

Functions such as TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, and SEARCH can be leveraged to extract email addresses from unstructured text and transform them into a usable format.

  • Simply, choose a cell (C5) and apply the below formula down.
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(B5)," ",REPT(" ",200)), (LEN(LEFT(B5,SEARCH("@",B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH("@",B5))," ", "")))*200+1, 200)), ",", "")

Formula Breakdown

  • LEFT(B5,SEARCH(“@”,B5)

Here, the formula finds the text from the beginning of cell (B5) up to the “@” symbol.

  • SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)

This section removes any spaces from the text obtained in step 1. It replaces each space (” “) with an empty string (“”).

  • LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”))

This portion calculates the length of the text obtained in step 2 and counts the number of characters in the text.

  • (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))

This step subtracts the length of the text without spaces from the length of the original text.

  • REPT(” “,200)

It generates a string consisting of 200 spaces repeating the space character (” “) 200 times.

  • SUBSTITUTE(TRIM(B5),” “,REPT(” “,200))

This replaces each occurrence of a single space in cell (B5) with 200 spaces. Then the TRIM function removes any leading or trailing spaces.

  • MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)

This extracts a portion of the modified text obtained in step 6. Hence, it starts from a specific position and retrieves a substring of length 200.

  • TRIM(MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)), “,”, “”)

This trims any leading or trailing spaces from the substring obtained in step 7.

  • SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)), “,”, “”)

This replaces any commas (“,”) in the trimmed substring with empty strings (“”).

Formula of TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SEARCH functions to extract email addresses

  • Then, hit the ENTER key and drag the fill handle down to get the final output.

Final result with extracting email addresses using Excel formulas


2.2 Applying TEXTJOIN, CHAR, IF, ISERROR, SEARCH, TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SUBSTITUTE Functions

If you are looking for a simple solution to extract email addresses from multiple cells then you can combine the TEXTJOIN, CHAR, IF, ISERROR, SEARCH, TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT functions too.

  • Similarly, select a cell (C5) and write the following formula inside the cell.
=TEXTJOIN(CHAR(10),TRUE,IF(ISERROR(SEARCH("@",B5:B12)),"",TRIM(MID(SUBSTITUTE(TRIM(B5:B12)," ",REPT(" ",200)),(LEN(LEFT(B5:B12,SEARCH("@",B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH("@",B5:B12))," ","")))*200+1,200))))

Formula Breakdown

  • SEARCH(“@”,B5:B12)

Here the SEARCH function searches for the “@” symbol in cells (B5:B12) and returns an array of values, indicating the position of “@” in each cell.

  • ISERROR(SEARCH(“@”,B5:B12))

This function checks if an error occurs in the search operation. It returns an array of TRUE or FALSE values, where TRUE represents an error and FALSE represents a successful search.

  • IF(ISERROR(SEARCH(“@”,B5:B12)),””,TRIM(MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200))))

Here an IF statement checks the array from step 2. If an error is found, it returns an empty string (“”). Otherwise, it proceeds with the next part of the formula.

  • LEFT(B5:B12,SEARCH(“@”,B5:B12))

This extracts the text from the beginning of each cell in the range B5 to B12 up to the “@” symbol.

  • SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)

This removes any spaces from the text obtained in step 4. It replaces each space (” “) with an empty string (“”).

  • LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””))

This calculates the length of the text obtained in step 5 for each cell and returns an array of values representing the length.

  • (LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1

This part calculates the starting position for the MID function. Then, it subtracts the length of the text without spaces from the length of the original text and multiplies the result by 200.

  • REPT(” “,200)

This generates a string consisting of 200 spaces. After that, it repeats the space character (” “) 200 times.

  • SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200))

This replaces each occurrence of a single space in the original text (B5:B12) with 200 spaces. The TRIM function removes any leading or trailing spaces.

  • MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200)

This extracts a portion of the modified text obtained in step 9. It starts from the position calculated in step 7 and retrieves a substring of length 200 for each cell.

  • TEXTJOIN(CHAR(10),TRUE,IF(ISERROR(SEARCH(“@”,B5:B12)),””,TRIM(MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200))))

This function concatenates the array of trimmed substrings obtained in step 11 into a single text string, separated by a line break (CHAR(10)). The TRUE argument indicates that empty values should be ignored.

Formula of TEXTJOIN, CHAR, IF, ISERROR, SEARCH, TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SUBSTITUTE functions to extract email addresses

  • Gently, hit ENTER and pull the Fill Handle down to fill the column.
  • As a result, we have successfully extracted email addresses from multiple cells.

Final result with extracting email addresses using Excel formulas


2.3 Combining SEARCH, ISERROR, IF and TEXTJOIN Functions

With the proper combination of the SEARCH, ISERROR, IF and TEXTJOIN functions you can extract email addresses.

  • In the same fashion, choose a cell (C5), write the below formula and drag the FIll handle down.
=IF(ISERROR(SEARCH("@", B5)), "", MID(B5, SEARCH(",", B5) + 2, LEN(B5) - SEARCH(",", B5) - 1))

Formula Breakdown

  • ISERROR(SEARCH(“@”, B5))

The SEARCH function searches for the “@” symbol in cell (B5) and returns the position of “@” in the text. The ISERROR function checks if an error occurs in the search operation.

  • IF(ISERROR(SEARCH(“@”, B5)), “”, …)

This is an IF statement that checks if an error occurs. If an error is found it returns an empty string (“”).

  • MID(B5, SEARCH(“,”, B5) + 2, LEN(B5) – SEARCH(“,”, B5) – 1)

Finally, the extraction of a portion of the text in cell (B5) happens. Then, it starts from the position calculated in step 2 and retrieves a substring of length obtained.

Formula of IF, ISERROR, SEARCH, MID, LEN functions to extract email addresses

  • Finally, we have the extracted email addresses in our hands.

Final output with extracting email addresses using Excel formulas

Read More: How to Format a Column for Email Addresses in Excel


3. Extracting Email Addresses from Multiple Columns

We can do it following three ways-


3.1 Using TEXTJOIN, IF, ISERROR, SEARCH Functions Together

In order to extract email addresses from multiple cells with one click you can use the combination of TEXTJOIN, IF, ISERROR, and SEARCH functions.

  • Choose a cell (F5), write the below formula, and hit ENTER to get the output.
=TEXTJOIN(", ", TRUE, IF(ISERROR(SEARCH("@", B5:D12)), "", B5:D12))

Formula Breakdown

  • ISERROR(SEARCH(“@”, B5:D12))

The SEARCH function searches for the “@” symbol in cells (B5:D12) and returns the position of “@” in the text. The ISERROR function checks if an error occurs in the search operation.

  • IF(ISERROR(SEARCH(“@”, B5:D12)), “”, B5:D12)

Here the IF statement checks if an error is found and returns an empty string (“”). Otherwise, it returns the corresponding cell value from the cells (B5:D12).

  • TEXTJOIN(“, “, TRUE, IF(ISERROR(SEARCH(“@”, B5:D12)), “”, B5:D12))

This function concatenates the array of values into a single text string, separated by a comma and a space (“, “).

Formula of TEXTJOIN, IF, ISERROR, and SEARCH functions to extract email addresses


3.2 Merging FILTER, TOCOL, ISNUMBER, SEARCH Functions

You can also combine FILTER, TOCOL, ISNUMBER, and SEARCH functions to extract email addresses from multiple locations in a spreadsheet.

  • Choosing a cell (F5), put the following formula and hit ENTER to get the result.
=FILTER(TOCOL(B5:D12),ISNUMBER(SEARCH("@",TOCOL(B5:D12))))

Formula Breakdown

  • SEARCH(“@”,TOCOL(B5:D12)

Here the TOCOL function converts the range B5 to D12 into a columnar array. Then the SEARCH function searches for the “@” in each cell on the columnar array.

  • ISNUMBER(SEARCH(“@”, TOCOL(B5:D12)))

Here the ISNUMBER function checks if an error occurs in the search operation and returns an array of TRUE or FALSE values.

  • FILTER(TOCOL(B5:D12), ISNUMBER(SEARCH(“@”, TOCOL(B5:D12))))

This part applies a filter to the columnar array using the TRUE and FALSE values and returns a new array that includes only the cells where “@” is found.

Formula of FILTER, TOCOL, ISNUMBER, and SEARCH functions to extract email addresses


3.3 Combining INDEX, MATCH, COLUMN, SEARCH, ISERROR, IF and MIN Functions

In some situations, you can also combine the INDEX, MATCH, COLUMN, SEARCH, ISERROR, IF, and MIN functions to separate email addresses.

  • Simply, select a cell (F5), place the following formula and press ENTER.
=INDEX(B5:D5,1,MIN(IF(ISERROR(SEARCH("@",B5:D5)),"",MATCH(COLUMN(B5:D5),COLUMN(B5:D5)))))

 

Formula Breakdown

  • ISERROR(SEARCH(“@”, B5:D5))

The SEARCH function returns the position of the “@” symbol within each cell, and the ISERROR function checks if an error occurs. If there is an error it returns TRUE, otherwise, it returns FALSE.

  • IF(ISERROR(SEARCH(“@”, B5:D5)), “”, MATCH(COLUMN(B5:D5), COLUMN(B5:D5)))

The IF function performs a conditional check. If the result of the previous step is TRUE, it returns an empty string (“”). Otherwise, it proceeds with the MATCH function.

  • MATCH(COLUMN(B5:D5), COLUMN(B5:D5))

The MATCH function is used to find the relative position of the current column within the range B5:D5. The COLUMN(B5:D5) part returns an array of column numbers {2, 3, 4}, and MATCH compares each column number with the array itself.

  • MIN(IF(ISERROR(SEARCH(“@”, B5:D5)), “”, MATCH(COLUMN(B5:D5), COLUMN(B5:D5))))

Finally, the MIN function is used to find the smallest value from the array returned by the previous step. If there are no errors, it returns the position of the leftmost column with the “@” symbol.

  • INDEX(B5:D5,1,MIN(IF(ISERROR(SEARCH(“@”,B5:D5)),””,MATCH(COLUMN(B5:D5),COLUMN(B5:D5)))))

The INDEX function is used to retrieve the value from the range B5:D5. It takes the row number as 1 and the column number as the result of the previous steps.

Formula of INDEX, MATCH, COLUMN, SEARCH, ISERROR, IF, and MIN functions to extract email addresses

  • Finally, we have successfully extracted email addresses from multiple cells. It’s that simple.

Final output with extracting email addresses from Excel

Read More: Formula to Create Email Address in Excel


4. Applying VBA Code with User-Defined Function

When it comes to extracting email addresses from a column of data in Excel, sometimes the built-in functions may not provide the level of flexibility or customization required. In such cases, Visual Basic for Applications (VBA) can be employed to create a user-defined function (UDF) that automates the extraction process.

  • First, open the VBA window and insert a module to write a VBA code.
  • Then, inside the module put the below code down and save it.
Function Extract_Email_Address(emailString As String) As String
emailPattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}\b"
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = emailPattern
.Global = True
End With
Set matches = regex.Execute(emailString)
If matches.Count > 0 Then
Extract_Email_Address = matches(0)
Else
Extract_Email_Address = ""
End If
End Function

VBA code to extract email addresses using user defined function

  • Now, coming back to the workbook, choose a cell (D5) and write the newly created user-defined function.
=Extract_Email_Address(B5)

Applying formula with user defined function to extract email addresses

  • Simply, hit ENTER and drag down the fill handle to extract email addresses.

Final output with extracting email addresses applying VBA code


How to Extract Username from Email Addresses in Excel

Suppose we have a dataset of Email Addresss and some Contact Numbers with a separator in the same cell. Now, we will extract only the username from the email addresses in Excel.

Sample dataset to extract username from email addresses

  • First, select the whole table and visit the Text to Columns feature from the Data tab.

Choosing Text to Column feature from the Data tab

  • From the new window, choose Delimited and hit Next.

Selecting Delimited and pressing Next option

  • Hence, checkmark the Comma option and put @ in the Other section.

Choosing Delimiters and hitting Next option

  • Thereafter, choose your destination cell, skip some columns, and hit Finish.

Selecting a destination cell and skipping column to get the username

  • Finally, we have the user name extracted from the email addresses in Excel.

Final result with extracting username from the email addresses


Things to Remember

  • If you are using Windows then you can apply the Flash fill feature for extracting email addresses. In Mac devices, the flash fill feature won’t work.

Frequently Asked Questions

1. Can I extract email addresses with specific criteria, such as filtering by domain or filtering out duplicates?

You can filter by domain using text filters or utilize built-in functions to remove duplicates from the extracted email addresses.

2. Can I extract email addresses from multiple Excel files at once?

you can consolidate the data from multiple files into a single Excel file and then extract the email addresses.

3. Can I automate the process of extracting email addresses from Excel?

With automation, you can create scripts or programs to extract email addresses from Excel files in a batch process, saving time and effort.


Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

In conclusion, extracting email addresses from Excel provides a streamlined approach for managing, analyzing, and utilizing email data. You can integrate email addresses into various applications for effective communication. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience.


Related Articles:

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo