Create standardized email addresses from first and last name columns

Why simple LOWER(A2 & "." & B2 & "@" & C2) breaks in HR rosters

These examples implement one organization’s naming policy: trim names, remove spaces and apostrophes, and join first and last names. Apostrophes are not universally invalid in email addresses; removing them is a policy choice.

Cleaning FirstName and LastName with TRIM and nested SUBSTITUTE calls removes spaces and apostrophes before joining with "." and "@".

Normalized first.last@domain formula

This constructs candidate addresses from the illustrated ASCII names. It does not create mailboxes, verify deliverability, enforce all email syntax rules, or resolve duplicate names.

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

Single-column full-name variant

When full names live in one cell, TEXTBEFORE(TRIM(name), " ") grabs the given name and TEXTAFTER(TRIM(name), " ", -1) grabs the final surname.

The one-cell variant uses only the first and last space-separated tokens. It loses middle names and compound surnames, so prefer separately supplied name fields. Apply lowercasing only under your own mailbox naming policy.

=LOWER(TEXTBEFORE(TRIM("  Marco   Rossi "), " ") & "." & TEXTAFTER(TRIM("  Marco   Rossi "), " ", -1) & "@" & TRIM(C2))

Further reading

• Microsoft Support — Combine text from two or more cells into one cell