Combine First and Last Name in Excel and Google Sheets
Purpose
Combining first and last names is one of those tasks that looks trivial until you actually try to do it…
Combining first and last names is one of those tasks that looks trivial until you actually try to do it cleanly across a thousand rows with inconsistent data. I have seen spreadsheets where names were joined with no space, with two spaces, with trailing spaces from the source system, or where some rows had a middle name field that was sometimes empty. Each of these creates slightly different problems. The simplest approach — using & to join the two cells with a space between them — works perfectly when the data is clean. TEXTJOIN is the better choice when some cells might be blank because it automatically skips empty values without leaving double spaces. This post covers both approaches plus how to format names in Last, First order and how to add titles like Mr or Dr at the front.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=A2&" "&B2
TEXTJOIN is available in Excel 2019+ and Google Sheets. For older Excel versions use the & operator or CONCATENATE.
Arguments
| Argument | Required | Description |
|---|---|---|
| text1 | Required | The first piece of text to join. Usually the first name cell. Can also be a literal text string in quotes. |
| delimiter | Optional | The character to place between each piece. Use " " for a space, ", " for comma-space, or any other separator. |
| ignore_empty | Optional | TRUE skips blank cells without adding extra delimiters. FALSE includes a delimiter even when a cell is empty. Almost always use TRUE. |
| text2 | Required | The second piece of text. Usually the last name cell. TEXTJOIN accepts up to 252 text arguments. |
How it works
The & operator in Excel and Google Sheets concatenates — joins — text strings together. When you write =A2&" "&B2, you are joining three things: the content of A2, a literal space character in double quotes, and the content of B2. The result is the first name, a space, and the last name as a single text string. TEXTJOIN works similarly but takes a delimiter as the first argument, followed by an ignore_empty flag, followed by the values to join. The ignore_empty argument is the key advantage — when set to TRUE, TEXTJOIN skips any blank cells in the list without adding extra delimiters. If you have a first name, optional middle name, and last name, TEXTJOIN handles rows where the middle name is empty without producing double spaces.
Examples
1
Combine first and last name from separate columns into a single full name column.
| A | B | C | |
|---|---|---|---|
| 1 | First Name | Last Name | Full Name |
| 2 | Alice | Chen | =A2&" "&B2 |
| 3 | Bob | Smith | Bob Smith |
| 4 | Carol | Jones | Carol Jones |
| 5 | David | Kim | David Kim |
| 6 | Eve | Taylor | Eve Taylor |
| 7 | Frank | Brown | Frank Brown |
| 8 | Grace | Lee | Grace Lee |
| 9 | Hank | Patel | Hank Patel |
| 10 | Iris | Wong | Iris Wong |
| 11 | James | Miller | James Miller |
Row 2: Alice Chen
— The & operator joins A2 (Alice), a literal space " ", and B2 (Chen) into the single string Alice Chen.
Copy this formula down the column by double-clicking the fill handle. Each row picks up its own A and B cell values automatically.
2
Create Last, First format for a sorted employee register.
| A | B | C | |
|---|---|---|---|
| 1 | First Name | Last Name | Formal Name |
| 2 | Alice | Chen | =B2&", "&A2 |
| 3 | Bob | Smith | Smith, Bob |
| 4 | Carol | Jones | Jones, Carol |
| 5 | David | Kim | Kim, David |
| 6 | Eve | Taylor | Taylor, Eve |
| 7 | Frank | Brown | Brown, Frank |
| 8 | Grace | Lee | Lee, Grace |
| 9 | Hank | Patel | Patel, Hank |
| 10 | Iris | Wong | Wong, Iris |
| 11 | James | Miller | Miller, James |
Row 2: Chen, Alice
— Last name first, then a comma and space, then first name — the standard format for surname-sorted registers.
This format sorts correctly when you sort the Formal Name column A to Z — it will order by surname rather than by first name.
Common use cases
1. Build a display name for a staff directory from separate first and last name fields
=A2&" "&B2
2. Create formal Last, First format for a sorted surname register
=B2&", "&A2
3. Generate email-style names with first initial and last name
=LEFT(A2,1)&"."&LOWER(B2)&"@company.com"
4. Combine title, first name, and last name for formal correspondence labels
=C2&" "&A2&" "&B2
5. Join first name, optional middle name, and last name skipping blanks
=TEXTJOIN(" ",TRUE,A2,B2,C2)
Common errors
Double space in the result
The first name or last name cell has a trailing or leading space, causing two spaces when the literal space is added between them.
Fix: Wrap each cell in TRIM: =TRIM(A2)&" "&TRIM(B2). TRIM removes all leading, trailing, and duplicate internal spaces.
Numbers or dates show wrong format
When you concatenate a number or date cell with text, Excel converts it to a number which may not be formatted the way you expect. A date might show as 45292 instead of 2024-01-15.
Fix: Wrap numbers or dates in TEXT() to control the format: =A2&" "&TEXT(C2,"yyyy-mm-dd") for a date.
Result is a text string that cannot be sorted correctly
The combined name is text and sorts alphabetically by first character, which means sorting puts Alice Brown before Bob Anderson. For a sorted list by surname, use the Last, First format instead.
Fix: Use =B2&", "&A2 to produce Anderson, Bob format which sorts correctly by surname.
Tips and variations
Use TEXTJOIN for cleaner handling of optional name parts
When middle names or suffixes are sometimes blank, TEXTJOIN with ignore_empty=TRUE handles this perfectly. It skips empty cells without adding extra spaces, so rows with and without a middle name both look clean without any IF logic.
=TEXTJOIN(" ",TRUE,A2,B2,C2)
Use LOWER and PROPER to fix capitalisation
Data imported from systems often comes in ALL CAPS or all lowercase. Wrap the cells in PROPER to title-case them before joining: =PROPER(A2)&" "&PROPER(B2). This gives Alice Chen rather than ALICE CHEN or alice chen.
=PROPER(A2)&" "&PROPER(B2)
Build an email address from name components
Use the name-joining technique to build email addresses programmatically: =LOWER(LEFT(A2,1))&"."&LOWER(B2)&"@company.com" creates a.chen@company.com from Alice and Chen. Add a column for this, review it, and use it to auto-populate an email field.
=LOWER(LEFT(A2,1))&"."&LOWER(B2)&"@company.com"
Excel vs Google Sheets
Excel vs Google Sheets
The & operator and CONCATENATE work identically in Excel and Google Sheets. TEXTJOIN is available in Excel 2019+ and all versions of Google Sheets — it is not available in Excel 2016 or earlier, where & or CONCATENATE is the only option. Formulas using & copy between applications without changes.
Frequently asked questions
Use the TEXTJOIN or CONCAT function, or simply use & to join them: =A1&" "&B1. This joins the first name in A1, a space, and the last name in B1. For cleaner syntax =TEXTJOIN(" ",TRUE,A1,B1) does the same but scales more easily when you have multiple name parts.
CONCATENATE is the older function available in all Excel versions. CONCAT is the newer version available in Excel 2019 and later which also accepts ranges. & is the simplest operator and works in all versions. For combining two cells & and CONCATENATE are equivalent. Use CONCAT when you want to join a range of cells.
Reverse the argument order and add a comma: =B1&", "&A1. This puts the last name first, then a comma and space, then the first name. Useful for formal directories and reports where surname-first ordering is standard.
Add the title cell or literal text as the first piece: =C1&" "&A1&" "&B1 where C1 contains the title. For a literal title: ="Dr "&A1&" "&B1. Include the space inside the string so words do not run together.
Use TEXTJOIN with the ignore_empty argument set to TRUE: =TEXTJOIN(" ",TRUE,A1,B1). If either cell is blank, TEXTJOIN skips it without adding an extra space. This is the cleanest solution when name data is sometimes incomplete.