Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))

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

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

See it work

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

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.

=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))

Result"mlopez"

  1. A2"Maria"

    The value in A2.

  2. LEFT(A2,1)"M"

    LEFT: Characters from the start of text.

    • textA2"Maria"
    • num_chars1
  3. B2"Lopez"

    The value in B2.

  4. SUBSTITUTE(B2,"'","")"Lopez"

    SUBSTITUTE: Replaces matching text.

    • textB2"Lopez"
    • old_text"'"
    • new_text""
  5. LEFT(A2,1)&SUBSTITUTE(B2,"'","")"MLopez"

    The & operator joins as text.

  6. LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))"mlopez"

    LOWER: Converts to lowercase.

    • textLEFT(A2,1)&SUBSTITUTE(B2,"'","")"MLopez"

In Google Sheets

The same formula works in Google Sheets: =LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'","")). 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(LEFT(A2,1)&LEFT(SUBSTITUTE(B2,"'",""),7))mlopez, joneil, msmith…Different resultCaps 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(…," ","").

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 a username from first initial and last name

In C2, create a lowercase username from the first letter of the first name and the last name without apostrophes. Fill down to C8.

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)).

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.