How to create email addresses from names in Excel

[email protected] with & and LOWER

The sheet is a list of new starters who need accounts. The & operator joins text: first name, a dot, last name, the @ sign, and the domain. LOWER makes the whole address lowercase, because "Maria.Lopez" and "maria.lopez" look like two different people to a mail-merge list or a duplicate check, even though mail servers treat them the same in practice.

Keep the domain in its own cell (G1 here) and lock it with $G$1 so it stays put when you fill down. When the company adds a second domain or you reuse the sheet for a client, you change one cell instead of every formula. Type only the domain in G1, without the @, or the addresses come out with "@@".

Remove spaces and apostrophes with TRIM and SUBSTITUTE

The plain formula breaks on real names. "Ana Sofia" gives "ana sofia.reyes@", with a space, which is not a valid address. "Daniel " with a trailing space gives "daniel .kim@". "O'Neill" gives "sean.o'neill@". An apostrophe is technically allowed in an email address, but many sign-up forms and older systems reject it, so most companies drop it.

Clean each part first. TRIM removes the stray spaces at the ends, SUBSTITUTE(text," ","") removes the spaces inside, and a second SUBSTITUTE removes the apostrophe. Names pasted from Word or Outlook often contain the curly apostrophe (’) instead of the straight one, so add a third SUBSTITUTE for it if your list came from a document.

Hyphens are valid, so "Shah-Patel" stays as priya.shah-patel. If your company's rule drops hyphens too, add SUBSTITUTE(...,"-","").

First initial plus surname

For the jdoe style, take the first letter with LEFT and join the cleaned surname. TRIM the first name before LEFT, or a name typed with a leading space gives a space as the initial.

This format collides much more often than first.last. Maria Lopez and Mark Lopez on rows 8 and 9 both become mlopez, and so does every M. Lopez who joins later. If you use it, the duplicate check in the next section is not optional.

Duplicates: add a number with COUNTIF

Two people called Maria Lopez cannot share an address. Build the cleaned handle in a helper column first. Column C holds =LOWER(SUBSTITUTE(SUBSTITUTE(TRIM(A2)&"."&TRIM(B2)," ",""),"'","")) filled down. Then count how many times the handle has appeared so far with a growing range, C$2:C8. The first Maria Lopez counts 1 and gets no number; the second counts 2 and becomes maria.lopez2.

The $ on the 2 keeps the start of the range fixed while the end moves with each row. That is what makes it a running count instead of a total.

Two things the formula cannot know. It only sees this list, so check new handles against the existing staff directory too, for example with COUNTIF(Directory!A:A,C2&"@"&$G$1). And the numbers depend on row order: sort the list and a different Maria becomes maria.lopez2. Once accounts are created, paste the email column as values (Ctrl+C, then Ctrl+Shift+V or Home > Paste > Values) so they never change.

Accents, and the whole list at once

"Chloé" becomes chloé.dubois. Some mail systems accept accented addresses, but many do not, and Excel has no function that strips accents. Replace each accented letter you have with SUBSTITUTE: one SUBSTITUTE per letter (é, è, ü, ñ and so on), nested or applied in turn. Flash Fill will not remove them reliably either.

In Excel 365 or 2021 one formula can build every address: =LOWER(SUBSTITUTE(SUBSTITUTE(TRIM(A2:A10)&"."&TRIM(B2:B10)," ",""),"'",""))&"@"&G1 spills one address per row. It is convenient for a quick list, but the running COUNTIF needs real ranges, so use the helper column when duplicates are possible.

Make the addresses clickable with HYPERLINK

HYPERLINK(link, friendly_name) turns text into a link. Put "mailto:" in front of the address and clicking the cell opens a new email to that person in the default mail app. The second argument is what the cell shows, so pass the plain address there, or the cell shows the mailto: prefix too.

For a mail merge you do not need this: Word reads the plain text column. And a HYPERLINK cell is still a formula, so when you paste the column as values, keep the plain address column, not the link column.

Which method to pick

For a one-off list with clean names, =LOWER(A2&"."&B2&"@"&$G$1) is enough. For real staff or customer data, always use the TRIM and SUBSTITUTE version, build it in a helper column, and add the COUNTIF number. Prefer first.last over first initial plus surname, since it collides far less. Paste the final addresses as values before you send them to IT or a mailing tool, so later edits to names do not change addresses that already exist.