How to Separate Email Addresses in Excel: 5 Quick Solutions

Do you regularly waste hours splitting email addresses in Microsoft Excel? You mustn’t let this tedious task hold you back from doing what you do best. Today, I show you how to separate email addresses in Excel automatically using formulas and other intuitive Excel tools.

Imagine how much more productive you could be if you knew how to quickly and easily separate email addresses in Excel. This valuable skill can streamline your workflow, boost your data analysis capabilities, and free up your time for personal or professional needs.

The Excel app comes with all the built-in features you need to extract emails from long texts or separate email user names and domains, or split emails from names. Find below the various ways to do these routine tasks at home or office.

Reasons to Separate Email Addresses in Excel

Here are some reasons why you must learn how to separate email addresses in Excel:

• Extract, transform, and organize raw database data into human-readable structures that also look good
• Helps to reduce errors and duplication in email-based marketing campaigns
• A non-technical audience can easily locate the email data that you want to show them
• Saves time by automating a tedious and repetitive task
• You can invest the saved time in more important things than mundane Excel data entry
• If you need a clean database of names, emails, and phone numbers, separating emails in Excel is the best bet

1. Separate Email Using TEXTSPLIT

Suppose, you downloaded employee email data from a learning management system (LMS) and find that the name of the tool proceeds the email addresses. Now, you can’t use the emails in another app unless you can get rid of the tool name from the email addresses. On top of that, there are 1,000 email addresses that you need to separate from the tool name. Here’s how you must proceed:

1. To the right side of the existing email data, create two new columns: Undesirables and Email Address.
2. Below the Undesirables column header, type the following formula:

=TEXTSPLIT(A2,"-")

3. Simply hit the Enter key and Excel will separate the garbage data from email addresses. It also puts the emails under the Email Address column.
4. Paste the same formula for all other cells. Emails and attached text strings will separate automatically.
5. You can’t directly make use of this cleaned email data. You must convert the data into text.

6. To do this, copy the entire email column and paste it under the Emails in Text column by pressing Ctrl + Alt + V.
7. When the Paste Special dialog box shows up, press V once more.
8. Now, hit the OK button to paste values only.

TEXTSPLIT function is only available in Excel 365 desktop app and Excel for the web app. So, you can’t use this method on Excel 2021, Excel 2019, etc.

2. Use Text to Column to Separate Email Addresses

Since you’re using Excel 2021 or earlier software and are unable to utilize the TEXTSPLIT formula, you can rely on the Text to Column feature of Microsoft Excel. Find below how to use Text to Column to extract email addresses from strings of undesirable data:

1. Select the whole column where you got email addresses along with undesirable text strings.
2. If the column is much longer, you can use the Ctrl + Shift + Down Arrow keys altogether to quickly select the column until the last cell contains values.
3. Now, click the Data tab on the Excel ribbon menu and then select Text to Columns inside the Data Tools block.

4. You should now see the Convert Text to Columns Wizard. Under the Original data type section, click the radio button for the Delimited option. Click Next to go ahead.

5. On Step 2 of 3 dialog box, checkmark the Other checkbox under the Delimiters section. In the empty box to the right of the Others option, type in the delimiter just before the email address.
6. It could be any character like a space, tab, hyphen, underscore, @, asterisk, and so on. In the current example, it’s a hyphen.
7. As soon as you set the delimiter character in the designated box, the Data preview section shows how the output data will look like.
8. If you think the organization of the data is satisfactory, click Next.

9. In Step 3 of 3 dialog box, you can choose the destination cell range where you want Excel to perform the Text to Column conversion. By default, it’s the first cell that you select.
10. Once again, check the Data preview section. If everything looks good, hit the Finish button.

11. Excel immediately transforms the raw data into well-organized data sets where email addresses show up in a different column than the undesirable text strings.
12. In this process, you automatically get readily usable email addresses in text format.

The Text to Column command of the Excel Data Tools block is available in most Excel desktop and web app versions. So, this method is more accessible than any other hacks to extract emails mentioned in this article.

3. Separate Email Addresses in Excel Using Flash Fill

Another quick way to separate email addresses from junk texts in Excel is by using the Flash Fill tool. However, the feature is only available in Excel 2013 and later and the latest Excel for web app. Here’s how you can use Flash Fill to extract emails from raw data:

1. Create a new column on the right side of the existing data set.
2. Now, manually separate the first email from junk text by simply typing the email address in the new column.

3. Then, go to the Data tab and then select the Flash Fill command inside the Data Tools block.

4. Instantly, you should see that Excel separates the email addresses from the junk text strings.
5. The separated email addresses are in the text format so you can use them for further data analysis or visualization on Excel immediately.

4. Separate Email Addresses Using Power Query

Are you importing email address data of customers, research subjects, or employees from an external file like CSV or TSV or an external database like Microsoft Access or SQL?

Also read: How To Open A TSV File In Excel

You can separate the email addresses using Power Query, a powerful data transformation tool that also lets you clean data before importing it to an Excel worksheet. Here is a step-by-step explanation of the process:

1. If you’re importing data from an external source, just click the Data tab and then hit the Get Data button within the Get & Transform Data block.
2. Then, choose the desired source of data under the Get Data drop-down list. For example, you’ve got choices like From File (XLSX, JSON, TSV, CSV, etc.), From Azure SQL Database, From SharePoint Online List, and so on.

3. The data should now load on the Power Query Editor tool of Excel.
4. Now, right-click on the column that contains email addresses along with undesirable text strings.
5. On the context menu that shows up, hover the cursor over Split Column and click By Delimiter.

6. Then, in the Split Column by Delimiter dialog box, click the Custom drop-down list and see if the delimiter is already there. If not, enter the custom delimiter in the marked box.
7. On the Split at section, choose an appropriate command like the Left-most delimiter.
8. Hit the OK button to split the columns inside the Power Query tool.

9. The Power Query tool shows up again but this time there are two columns for the data you loaded. On the left-side column, you find the undesirable text strings and on the right-side column, you find the email addresses you need.
10. Right-click the unwanted data column and choose Remove from the context menu.

11. Finally, click File and then select Close & Load to import transformed email address data into your Excel worksheet.

5. Separate Email Addresses Using Office Scripts

If you want to automate the email address separating task on Excel 365 desktop and Excel on the web app, you can use the following Office Scripts method. You must have Microsoft 365 subscription to use this premium feature of Excel.

1. Click Automate on the Excel ribbon and select New Script.

2. Copy and paste the following Office Scrpt inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Text to columns on range A2:A7 on selectedSheet
	for (let row = 0; row < selectedSheet.getRange("A2:A7").getRowCount(); row++) {
		let sourceRange = selectedSheet.getRange("A2:A7");
		let destinationRange = selectedSheet.getRange("A2");
		let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split("-")
		destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
	}
}

3. You must change these code fields with an appropriate range of data according to your Excel worksheet:
   • range A2:A7
   • getRange("A2:A7")
4. Click Save script inside the Code Editor.
5. Now, hit the Run button to execute the Excel Automate or Office Script program to separate email addresses.

6. The script extracts the email addresses from the raw data and saves them as texts in the column next to the source data range.

Conclusions

So, that’s pretty much all the popular methods that experts use to separate email addresses in Excel. Depending on the volume of data and the scenario, you need to choose the method that suits your needs.

The TEXTSPLIT formula-based method is good for small to medium-sized Excel worksheets. Then, you can go for the Text to Column or Flash Fill method if you like a GUI-based interface. Finally, if you need advanced features, choose Power Query and Office Scripts.

Don’t forget to leave comments in the box below to let us know about your experience of following the above methods.