# How to create a username from first initial and last name in Excel

Take the first letter of the first name and the whole last name, in lowercase: =LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'","")).

**Excel:** `=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| First | Last | Username |
| Maria | Lopez |  |
| James | O'Neil |  |
| Mary Ann | Smith |  |
| Lars | Eriksen |  |
| Liam | Fitzgerald |  |
| Aiko | Tanaka |  |
| Mark | Lopez |  |

Formula in C2, filled to C8: `=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))`

Result: mlopez; joneil; msmith; leriksen; lfitzgerald; atanaka; mlopez

## How it works

LEFT with 1 takes the first initial, SUBSTITUTE strips the apostrophe from names like O'Neil, and LOWER makes the result lowercase so "MLopez" and "mlopez" can't both exist.

Notice rows 2 and 8: Maria Lopez and Mark Lopez both get "mlopez". Usernames have to be unique, so check for that before you use them.

## Other ways

- `=LOWER(LEFT(A2,1)&LEFT(SUBSTITUTE(B2,"'",""),7))`: Caps the username at eight characters, for systems with a length limit. (Long last names are cut: lfitzger instead of lfitzgerald.)

## Common mistakes

- Duplicates are easy to miss. Add a number to repeats: in D2, `=C2&IF(COUNTIF($C$2:C2,C2)>1,COUNTIF($C$2:C2,C2),"")` turns the second "mlopez" into "mlopez2".
- Spaces in last names like "Van Dyke" end up in the username. Add `SUBSTITUTE(…," ","")`.

## Questions

**How do I use first name plus last initial instead?**

Swap the parts: `=LOWER(A2&LEFT(B2,1))` gives "marial".

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

Use the first letter and the last word: `=LOWER(LEFT(A2,1)&TEXTAFTER(A2," ",-1))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/create-username-from-first-initial-and-last-name