Excel can combine a first name in one cell and a last name in another, or split a full name into separate columns.
The right formula depends on your Excel version and how consistently the names are entered.
Combine first and last names in Excel
If the first name is in A2 and the last name is in B2, enter this formula in C2:
=A2&" "&B2
The ampersand joins text, and the quoted space puts a space between the names.
For example, if A2 contains Maria and B2 contains Garcia, C2 returns Maria Garcia.
Copy the formula down to combine the remaining rows.
You can also use the CONCAT function in current Excel versions:
=CONCAT(A2," ",B2)
For a formula that skips empty cells and avoids an extra space when one name is missing, use TEXTJOIN:
=TEXTJOIN(" ",TRUE,A2,B2)
TEXTJOIN is available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365.
CONCAT is supported in Excel 2016 and later.
The ampersand formula works in older versions too.
Combine names when some cells are blank
The basic ampersand formula still returns a space if either name cell is empty.
To avoid that, use:
=TRIM(A2&" "&B2)
TRIM removes leading and trailing spaces and reduces repeated spaces between text.
If A2 is Maria and B2 is blank, the formula returns Maria, without a trailing space.
If both cells are blank, it returns an empty-looking result.
TEXTJOIN provides another option:
=TEXTJOIN(" ",TRUE,A2,B2)
Its second argument, TRUE, tells Excel to ignore empty cells.
This can be useful for combining more than two name parts.
For example, to join first, middle, and last names in A2:C2, use =TEXTJOIN(" ",TRUE,A2:C2).
Split a full name into first and last names
If A2 contains a full name such as Maria Garcia, Excel 365 and Excel 2024 can split it with TEXTBEFORE and TEXTAFTER:
=TEXTBEFORE(A2," ")
This returns the text before the first space: Maria.
To return the text after the first space, enter:
=TEXTAFTER(A2," ")
That formula returns Garcia for the example.
If the full name is in A2, put the first-name formula in B2 and the last-name formula in C2, then fill each formula down.
These formulas split at the first space.
For a name with a middle name, such as Maria Elena Garcia, TEXTBEFORE returns Maria, while TEXTAFTER returns Elena Garcia.
The latter is the remaining text after the first space, not necessarily just the surname.
Use TEXTSPLIT to divide names into columns
In Microsoft 365 and Excel 2024, TEXTSPLIT can separate a full name at each space.
Enter this formula in B2:
=TEXTSPLIT(TRIM(A2)," ")
For Maria Garcia, the results spill into adjacent cells as Maria and Garcia.
Make sure the cells to the right are empty, or Excel may display a #SPILL! error.
With three name parts, such as Maria Elena Garcia, the formula spills into three columns.
TRIM helps prevent accidental extra spaces from creating empty results between words.
TEXTSPLIT is not available in perpetual Excel versions before Excel 2024, or in Excel 2019 and earlier.
For those versions, use Text to Columns or the formulas below.
Split names in older Excel versions
Excel 2016 and earlier do not include TEXTBEFORE, TEXTAFTER, or TEXTSPLIT.
If names consistently contain just a first name and last name separated by one space, use these formulas instead.
To return the first name:
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)
To return the text after the first space:
=MID(TRIM(A2),FIND(" ",TRIM(A2)&" ")+1,LEN(TRIM(A2)))
These formulas trim extra spaces around the name.
They also avoid an error for a single-word entry: the first-name formula returns the whole entry, while the second returns a blank.
For a middle name, the second formula returns everything after the first space.
For a quick, no-formula option, select the full-name column and choose Data > Text to Columns.
Select Delimited, choose Space as the delimiter, and complete the wizard.
Check the destination range first: Text to Columns can overwrite cells to the right.
Handle names with middle names, titles, or multiple surnames
Name formats vary.
A formula that treats everything before the first space as the first name and everything after it as the last name will not correctly identify every person’s given names or family names.
For example, Dr. Maria Garcia may include a title, while Maria Elena Garcia may include a middle name.
If your data has separate columns for first, middle, and last names, combine only the fields you need.
For instance, with first name in A2, middle name in B2, and last name in C2, use:
=TEXTJOIN(" ",TRUE,A2:C2)
To build a display name that omits the middle name, use =TEXTJOIN(" ",TRUE,A2,C2).
Keeping the original name parts in separate columns makes it easier to format names for different reports without guessing where each part belongs.
Common formula problems
- Missing space: Include
" "between the cell references, as in=A2&" "&B2. - Extra spaces: Wrap the combined text in TRIM or use TEXTJOIN with its ignore-empty option set to
TRUE. #NAME?error: The function may not be available in your Excel version.
Use the ampersand formula for combining or the LEFT and MID formulas for splitting.#SPILL!error: Clear the cells where a dynamic-array formula such as TEXTSPLIT needs to return its results.- Unexpected split: A space-based formula splits on spaces, not on a person’s actual name structure.
Review names with middle names, prefixes, suffixes, or multiple parts.
Formulas return results that update when the source cells change.
To keep the combined names as fixed text, copy the results and use Paste Special > Values in the destination range.