In this article, we will show you how to extract names from email addresses in Excel using formulas and the Text to Columns feature. Simply follow the process below
Simply follow the steps below to extract names from email address in Excel using formulas.
Open the Excel document that contains the email addresses you want to work with. Ensure the email addresses are in a single column.
Right-click on the top of the column next to your emails and select 'Insert'. This will create a new column where the extracted names will be placed.
In the new column, type the formula `=LEFT(A1, FIND("@",A1)-1)`. Replace 'A1' with the cell reference of the first email address. This will extract everything before the "@" symbol.
Click on the bottom right corner of the cell with the formula and drag it down. This will apply the formula to all email addresses displaying the names in the new column.
Click on the cell next to the first extracted name. Enter the following complex formula to clean up the names by removing numbers and replacing underscores and periods with spaces:
```excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1, FIND("@",A1)-1), "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), "_", " "), ".", " ")
```
Replace `A1` with the actual cell reference. Press Enter, then drag the fill handle down to apply this to all names.
Check the extracted names for any errors or irregularities, such as misplaced spaces or special characters that did not get removed, and make adjustments as needed.
Simply follow the steps below to extract names from email address in Excel using the Text to Column feature.
Click on the column header containing your email addresses to highlight the whole column. Then, navigate to the Data tab on the Excel ribbon and click the 'Text to Columns' button.
In the Text to Columns wizard, select the 'Delimited' option. This choice is crucial for splitting text based on specific characters, such as the '@' in email addresses. Click 'Next' to proceed.
When prompted to choose delimiters, check the box next to 'Other' and type in the '@' symbol. This action instructs Excel to use the '@' symbol as the point to split the email address into two parts. Click 'Next' to continue.
In the wizard's next step, specify where you want the split data to appear. It's recommended to select a cell in a new column next to your emails. This prevents overwriting the original data. Confirm by clicking 'Finish'.
After splitting, some names might still have unwanted characters like periods or underscores.
Press Ctrl+H to open 'Find and Replace'. Enter the character you want to remove in 'Find what' and replace it with a space or leave it blank in 'Replace with'.
Review the names in the newly created column. If there are any inaccuracies or formatting issues, correct them manually to ensure all names are properly formatted.
We hope that you now have a better understanding of how to extract names from email address in Excel using formulas and the text to column feature. If you enjoyed this article, you might also like our article on how to extract domains from email addresses in Excel or our article on how to create a link to an Excel file in an email.
