How to Combine First and Last Name in Excel (and Google Sheets)
Combining a first‑name column with a last‑name column is a common task, but the best approach depends on the version of Excel (or Google Sheets) you’re using and on how clean your source data are. Below you’ll find the most reliable formulas, tips for handling blanks and extra spaces, and a quick‑fill alternative for one‑off jobs.
1. The Ampersand (&) Operator – Simple and Universal
The & operator concatenates text without needing a function name, so it works in every version of Excel (2007‑2024) and in Google Sheets.
Syntax
=FirstNameCell & " " & LastNameCell
" "is the literal space that separates the names.- Use
TRIMon each part if blanks or stray spaces are possible:
=TRIM(A2) & " " & TRIM(B2)
Example
| A (First) | B (Last) | C (Full Name) |
|---|---|---|
| John | Doe | =A2 & " " & B2 |
| Mary | O'Neil | =A3 & " " & B3 |
| Lee | Chen | =A4 & " " & B4 |
Result column C
| C (Full Name) |
|---|
| John Doe |
| Mary O'Neil |
| Lee Chen |
Why it’s handy
- No extra functions to remember.
- Works with numbers, dates, or text—Excel coerces non‑text automatically.
- Easy to extend:
=A2 & " " & C2 & " " & B2adds a middle name.
2. TEXTJOIN – One‑Delimiter, Blank‑Skipping Solution (Excel 365/2021+, Google Sheets)
When you need to concatenate multiple columns or want to ignore empty cells, TEXTJOIN is the cleanest option.
Syntax
=TEXTJOIN(delimiter, ignore_empty, range1, [range2], …)
delimiter– e.g.," "for a space.ignore_empty–TRUEskips blanks, preventing double spaces.range– any contiguous range (rows or columns).
Example
| A (First) | B (Last) | C (Full Name) |
|---|---|---|
| John | Doe | =TEXTJOIN(" ",TRUE,A2:B2) |
| Jane | =TEXTJOIN(" ",TRUE,A3:B3) |
|
| (blank) | Smith | =TEXTJOIN(" ",TRUE,A4:B4) |
Result column C
| C (Full Name) |
|---|
| John Doe |
| Jane |
| Smith |
Quick tip – To get “Last, First” just swap the order and change the delimiter:
=TEXTJOIN(", ",TRUE,B2,A2) (returns “Doe, John”)
Version note:
TEXTJOINis unavailable in Excel 2016 and earlier; useTRIM(A2 & " " & B2)as a fallback.
3. CONCAT / CONCATENATE – Legacy Compatibility
CONCATENATE works in all Excel versions (2007‑2024) and in Google Sheets, but it requires each cell to be listed individually. CONCAT (Excel 2016+ / Google Sheets) accepts a range but does not ignore blanks.
Example with CONCATENATE
=CONCATENATE(TRIM(A2), " ", TRIM(B2))
| A (First) | B (Last) | C (Full Name) |
|---|---|---|
| John | Doe | John Doe |
| Mary | (blank) | Mary |
When to use it
- When you must support Excel 2007‑2010.
- When you prefer an explicit function over the
&operator.
4. Adding Punctuation or Titles
You can insert commas, periods, or titles by concatenating the extra characters directly.
=TRIM(A2) & " " & LEFT(C2,1) & ". " & TRIM(B2)
=TRIM(D2) & " " & TRIM(A2) & " " & TRIM(B2)
| A (First) | B (Last) | C (Middle) | D (Title) | Result |
|---|---|---|---|---|
| John | Doe | Michael | Dr. | John M. Doe |
| Jane | Smith | Jane Smith |
Wrap the whole expression in TRIM() to clean up any double spaces when a middle name or title is missing.
5. Flash Fill – No Formula Needed
For a one‑off clean‑up, Flash Fill learns the pattern you type and fills the rest of the column automatically (Excel 2013+).
- In C2, type the exact combined name you want (e.g.,
John Doe). - In C3, start typing the next name (
Ja). Excel will preview the full result. - Press Enter to accept; the column fills down automatically.
| A (First) | B (Last) | C (Flash Fill) |
|---|---|---|
| John | Doe | John Doe |
| Jane | Smith | Jane Smith |
| Michael | Brown | Michael Brown |
When Flash Fill may fail
- Inconsistent patterns (some rows need commas, others don’t).
- Mixed data types (dates stored as numbers can confuse the algorithm).
If it misbehaves, simply press Ctrl + Z to undo.
6. Best‑Practice Checklist
| ✔️ Practice | Why it matters | Quick tip |
|---|---|---|
Use TEXTJOIN (or & with TRIM) to handle blanks |
Prevents double spaces and keeps formulas short | =TEXTJOIN(" ",TRUE,A2,B2) |
| Keep original columns | Deleting source data breaks formulas | Store the combined name in a new column |
| Prefer structured references in tables | Formulas self‑document and auto‑expand | =TEXTJOIN(" ",TRUE,[@FirstName],[@LastName]) |
| Centralize delimiters | Changing a separator later requires only one edit | Put the delimiter in D1 and use =TEXTJOIN(D$1,TRUE,…) |
Clean source data with TRIM/SUBSTITUTE |
Removes hidden spaces (CHAR(160)) that TRIM alone misses |
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) |
7. FAQ (2‑3 sentences each)
How do I combine first and last names in Excel?
Use =TRIM(A2) & " " & TRIM(B2) for maximum compatibility, or =TEXTJOIN(" ",TRUE,A2,B2) if you have Excel 365/2021 or Google Sheets and want blanks ignored automatically.
Can I add a middle name or title when merging names?
Yes—just concatenate the extra fields, e.g., =TRIM(A2) & " " & LEFT(C2,1) & ". " & TRIM(B2) for a middle initial, or =TRIM(D2) & " " & TRIM(A2) & " " & TRIM(B2) for a title. Wrap the whole expression in TRIM() to tidy up missing parts.
What if my name data is in different columns?
Reference each column in the same way, regardless of its position: =TRIM(FirstCol) & " " & TRIM(LastCol). If the columns are non‑adjacent, you can still use TEXTJOIN(" ",TRUE,A2,C2,B2).
Is there a way to do this automatically for many rows?
Enter the formula in the first row and drag it down, or convert the range to an Excel Table so the formula auto‑fills for new rows. TEXTJOIN can also join a whole column into one cell, e.g., =TEXTJOIN(", ",TRUE,A2:A20).
Do these methods work in Google Sheets as well?
All the formulas shown (&, TEXTJOIN, CONCATENATE) work unchanged in Google Sheets. The only exception is that Google Sheets lacks the newer Excel‑only functions LET, LAMBDA, or XLOOKUP, but they aren’t needed for simple name concatenation. Flash Fill is an Excel‑only feature; in Sheets you’d copy the formula down manually or use an array formula.