How to extract initials from a name in Excel

First and last name: LEFT and TEXTAFTER

The sheet is an attendee list for name badges, typed by hand, so it has every problem real lists have. LEFT(A2) with no length returns the first character, M. TEXTAFTER(A2," ") returns everything after the first space, "Lopez", and LEFT of that is L. Join them with &.

In Excel 2021 and earlier, find the space with FIND and take the character after it: =LEFT(A2)&MID(A2,FIND(" ",A2)+1,1) also gives ML and works in every version.

This formula only reads the first two words. On "Ana Sofia Reyes" it returns AS, which is right for some people and wrong for others, so decide which initials you want before you fill it down.

Every word: TEXTSPLIT with CONCAT or TEXTJOIN

For names with three or more parts, split the name into words and take the first letter of each. TEXTSPLIT(A4," ") gives {"Ana","Sofia","Reyes"}, LEFT works on the whole list at once and returns {"A","S","R"}, and CONCAT joins them into one text. =TEXTJOIN("",TRUE,LEFT(TEXTSPLIT(A4," "))) does the same thing.

This handles middle initials too. "Priya K. Sharma" gives PKS: the period stays behind because LEFT only takes the first character of "K.".

Without TEXTSPLIT (Excel 2021 and earlier), you need a FIND for each space. For exactly three words: =LEFT(A4)&MID(A4,FIND(" ",A4)+1,1)&MID(A4,FIND(" ",A4,FIND(" ",A4)+1)+1,1), which returns ASR but gives #VALUE! on a two-word name. For mixed lists in older versions, Flash Fill is easier: type ML next to Maria Lopez and ASR next to Ana Sofia Reyes, then press Ctrl+E. Check the results, because Flash Fill guesses the pattern and does not update when names change.

First and last initial only, skipping middle names

Monograms, badges and short codes often want two letters: first name and surname, whatever is in between. Take the first letter, then the first letter after the last space. A negative instance number in TEXTAFTER counts from the end, so TEXTAFTER(A6," ",-1) is "Sharma".

Note what counts as the surname. "Mary-Kate van der Berg" gives MB with this formula, because "Berg" comes after the last space. If your list has surnames with particles like van der, de la or bin, no formula can tell them apart from middle names, so check those rows by eye.

Uppercase and extra spaces

Hand-typed lists contain "james o'brien" in lowercase and names with spaces at the start or doubled in the middle. LEFT of a name that starts with a space returns the space, so =LEFT(A5)&LEFT(TEXTAFTER(A5," ")) on " Daniel Kim " returns two blank characters that look like an empty cell.

Wrap the name in TRIM first. TRIM removes spaces at both ends and turns every run of spaces inside the text into one. Wrap the result in UPPER so lowercase entries give capital initials. LET names the trimmed text once, so you do not repeat TRIM(A5) inside the formula.

TRIM does not remove the non-breaking space (CHAR(160)) that names copied from web pages often carry. If TRIM leaves a gap, use SUBSTITUTE(A5,CHAR(160)," ") inside it.

Initials with periods: M.L.

TEXTJOIN puts a delimiter between items, so join the letters with "." and add one more period at the end. Its second argument, TRUE, skips empty items, which matters with messy spacing.

The tempting shortcut =CONCAT(LEFT(TEXTSPLIT(A5," "))&".") adds a period after every piece, including the empty pieces between doubled spaces, and turns " Daniel Kim " into "..D...K..". Give TEXTSPLIT TRUE as its fourth argument (ignore empty) to fix it: =CONCAT(LEFT(TEXTSPLIT(A5," ",,TRUE))&".") returns D.K.

For "M. Lopez" style, join the first initial to the last word: =LEFT(A2)&". "&TEXTAFTER(A2," ",-1).

Hyphens and one-word names

"Jean-Luc Picard" splits on spaces into two words, so you get JP. To treat the hyphen as a word break as well, give TEXTSPLIT both delimiters in an array constant: =CONCAT(LEFT(TEXTSPLIT(A7,{" ","-"}))) returns JLP. Which one is correct depends on the person, so pick one rule for the list.

A single name like "Chen" has no space, so TEXTAFTER returns #N/A. Its sixth argument is what to return when the delimiter is missing. With "" there, the formula returns just C. The TEXTSPLIT version needs no fix: one word gives one initial.

Leave lowercase particles alone or not, on purpose. Without UPPER, "Mary-Kate van der Berg" gives MvdB, which some people prefer; with UPPER it is MVDB.

Which method to pick

In Excel 365 or 2024: =UPPER(CONCAT(LEFT(TEXTSPLIT(TRIM(A2)," ")))) for every word, or the LET version with TEXTAFTER and -1 for first and last only. In Excel 2021 or earlier: LEFT with MID and FIND for two-word names, and Flash Fill for mixed lists. Whatever you use, TRIM first, and look over three-part names and particles by hand, because only you know whether "Ana Sofia" is one first name.