Spreadsheet Practice

How to create email addresses from first and last names in Excel

Formula (Excel and Google Sheets)

=LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1

Join the names with a period, remove spaces and apostrophes, lowercase it and add the domain: =LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1.

Result on the example
one result per row in C2, filled down to C7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.

How it works

A2&"."&B2 builds "Mary Ann.Smith". The inner SUBSTITUTE removes the space in "Mary Ann", the outer one removes the apostrophe in "O'Neil", and LOWER makes it all lowercase. The domain comes from $F$1, locked so every row uses it.

=LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1

Result"maria.lopez@northwind.example"

  1. A2"Maria"

    The value in A2.

  2. A2&".""Maria."

    The & operator joins as text.

  3. B2"Lopez"

    The value in B2.

  4. A2&"."&B2"Maria.Lopez"

    The & operator joins as text.

  5. SUBSTITUTE(A2&"."&B2," ","")"Maria.Lopez"

    SUBSTITUTE: Replaces matching text.

    • textA2&"."&B2"Maria.Lopez"
    • old_text" "
    • new_text""
  6. SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'","")"Maria.Lopez"

    SUBSTITUTE: Replaces matching text.

    • textSUBSTITUTE(A2&"."&B2," ","")"Maria.Lopez"
    • old_text"'"
    • new_text""
  7. LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))"maria.lopez"

    LOWER: Converts to lowercase.

    • textSUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'","")"Maria.Lopez"
  8. LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@""maria.lopez@"

    The & operator joins as text.

  9. $F$1"northwind.example"

    The value in F1.

  10. LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1"maria.lopez@northwind.example"

    The & operator joins as text.

In Google Sheets

The same formula works in Google Sheets: =LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1. We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=LOWER(A2&"."&B2&"@"&$F$1)maria.lopez@northwind.example, james.o'neil@northwind.example, mary ann.smith@northwind.example…Different resultThe short version, fine when no name has spaces or punctuation. Keeps the space and the apostrophe: "mary ann.smith@…" and "james.o'neil@…".
=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))&"@"&$F$1mlopez@northwind.example, joneil@northwind.example, msmith@northwind.example…Different resultFirst initial plus last name, the other common company pattern. mlopez@northwind.example instead of maria.lopez@northwind.example.

Common mistakes

  • Two people with the same name get the same address. Check the new column with =COUNTIF($C$2:$C$7,C2)>1.
  • Accented letters (é, ñ) stay in the address. Replace them with SUBSTITUTE if your mail system doesn't accept them.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Create email addresses from first and last names

In C2, build the email address as first.last@domain in lowercase, with no spaces or apostrophes, using the domain in F1. Fill down to C7.

Questions

How do I do it from a full name in one cell?

Replace the space with a period: =LOWER(SUBSTITUTE(A2," ","."))&"@"&$F$1.

How do I make the addresses clickable?

Wrap the result in HYPERLINK: =HYPERLINK("mailto:"&C2,C2).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.