Create Email Addresses From Names in Excel

To create an email from a name in Excel, you join the first name, a dot, the last name and the company domain into one piece of text, then make it lowercase. The formula is =LOWER(A2&"."&B2&"@example.com"), and it takes a minute to set up for a list of any size. This guide is for anyone setting up new staff accounts, a mailing list or a test task in a job assessment.

The sample data#

All the examples use this small staff list. Column A holds first names and column B holds last names. Some names have the problems you will meet in real data: a trailing space after "Lopez", an apostrophe, and names with a space inside them.

The ␣ symbol shows the hidden space at the end of "Lopez ". You can't see it in Excel, but it will end up inside the address if you don't remove it.

How to create an email address from a first and last name#

Join the parts with the & operator, which glues pieces of text together. Text you type yourself, like the dot and the domain, goes in double quotes.

=A2&"."&B2&"@example.com"

For row 3 this returns James.O'Neil@example.com. It works, but it has capital letters and an apostrophe. Most email systems ignore capitals, but a lowercase list looks tidy and avoids duplicates that differ only in case. Wrap the formula in LOWER:

=LOWER(A2&"."&B2&"@example.com")

Now row 3 returns james.o'neil@example.com. To fill the whole column, select C2:C5 and press Ctrl+D on Windows or ⌘D on a Mac. You can also double-click the small square at the bottom-right of C2.

If you'd rather keep the domain in its own cell, say E1, use "@"&$E$1 instead of typing it. The $ signs lock the reference so it doesn't move when you fill down. Then you change the domain in one place and every address updates. For more ways to join text, see the guide to combining text from several cells.

How to remove spaces and apostrophes from the names#

Clean the names inside the formula, so the source data stays as it is. Three small functions do the work:

• TRIM removes spaces at the start and end of the text, and turns double spaces into single ones.
• SUBSTITUTE(text, old, new) replaces one piece of text with another. Replacing " " with "" deletes every space.
• LOWER turns every letter into lowercase.

Here is the cleaned version, written over two lines so it is easier to read:

=LOWER(SUBSTITUTE(SUBSTITUTE(
  TRIM(A2)&"."&TRIM(B2)," ",""),"'","")&"@example.com")

The inner SUBSTITUTE removes spaces and the outer one removes apostrophes. The results are:

TRIM is not strictly needed once SUBSTITUTE deletes every space, but keeping it makes the intent clear and protects you if you later swap the space for a dot. The cleaning text lesson covers TRIM, SUBSTITUTE and their friends in more detail.

How to use TEXTJOIN for a middle name#

TEXTJOIN joins a list of cells with the same separator between each one, and it can skip blank cells. The syntax is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Say column B holds an optional middle name and column C the last name. Some people have a middle name and some don't. With &, a blank middle name leaves two dots in a row. TEXTJOIN with TRUE skips the blank:

=LOWER(TEXTJOIN(".",TRUE,A2:C2))&"@example.com"

For "Maria", blank, "Lopez" this gives maria.lopez@example.com. For "Anna", "Lee", "Park" it gives anna.lee.park@example.com. TEXTJOIN needs Excel 2019, Excel 2021 or Microsoft 365. The TEXTJOIN function guide explains each argument.

Other email formats: initials and full names in one cell#

Companies use different patterns, and each one is a small change to the same formula.

LEFT(text, 1) returns the first character of the text. The guide on getting initials from a name goes further, including middle initials.

If the full name sits in one cell, like "Maria Lopez" in A2, split it first. In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER do this neatly:

=LOWER(TEXTBEFORE(A2," ")&"."&TEXTAFTER(A2," ",-1))&"@example.com"

TEXTBEFORE takes the text before the first space. TEXTAFTER with -1 takes the text after the last space, so a middle name is skipped. In older versions, use LEFT(A2,FIND(" ",A2)-1) for the first name.

How to check the list for duplicates#

Two people with the same name get the same address, and most systems will reject the second one. Add a check column next to the emails:

=COUNTIF($C$2:$C$100,C2)>1

TRUE means the address appears more than once. Fix those by hand, for example by adding a middle initial or a number. COUNTIF is not case-sensitive, so Maria.Lopez and maria.lopez count as the same address, which is what you want here.

Going the other way, from an address back to its parts, is just as common. See how to extract the domain from an email address.

Common problems when building emails#

Most broken addresses come from the data, not the formula. Check these:

1. Spaces inside the address. A space before or after a name sneaks in. Use TRIM, or SUBSTITUTE to remove every space.
2. Accented letters. Names like José or Zoë keep their accents. Many email systems don't accept them, so replace them with SUBSTITUTE or fix them by hand.
3. Missing @. Typing "example.com" instead of "@example.com" gives maria.lopezexample.com.
4. The formula shows instead of the result. The cell was formatted as Text before you typed. Change it to General and re-enter the formula.
5. You need values, not formulas. Before you send the list to another system, copy the column and paste it as values so the addresses don't change if the names do.

Flash Fill is a no-formula option: type the first address by hand, then choose Data > Flash Fill (Ctrl+E on Windows). Excel copies the pattern, but it guesses, so check odd names like "van Dijk" carefully.

Practise it#

Joining text with & and cleaning it with TRIM are skills that show up in many job tests. Practise joining text in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page, which builds Maria's address from a name with a hidden space.