How to Combine First and Last Name in Excel (5 Ways)

Combine First and Last Names in Excel Using Formulas and Functions or Flash Fill

by Avantix Learning Team | Updated September 20, 2023

Applies to: Microsoft^® Excel^® 2013, 2016, 2019, 2021 and 365 (Windows)

You can combine first and last name in Excel in several ways – using the CONCATENATE operator, the CONCATENATE function, the CONCAT function, the TEXTJOIN function or Flash Fill. These functions are often used to combine text in cells but you can also combine text with spaces, commas, dashes or another character. It's common to combine first and last names that appear in two columns into one new column. Some functions are only available in newer versions of Excel but the CONCATENATE operator and function are available in all versions.

It is important to note that combining cells is different from merging cells.

In this article, we'll review 5 ways to combine first and last names in Excel:

1. Create a formula using the CONCATENATE operator
2. Create a formula using the CONCATENATE function
3. Create a formula using the CONCAT function (available in 2019, 2021 and 365)
4. Create a formula using the TEXTJOIN function (available in 2019, 2021 and 365)
5. Run Flash Fill

If you want to combine dashes, commas, spaces or other text in a formula, you need to enter it in quotation marks or double quotes (such as ",").

You can also include other functions in formulas such as TRIM and UPPER.

Recommended article: How to Merge Cells in Excel (4 Ways)

Do you want to learn more about Excel? Check out our online (virtual classroom) or in-person Excel courses >

1. Combine first and last name using the CONCATENATE operator (&)

You can use the CONCATENATE operator (&) to combine cells containing first and last name into one cell using a formula. Using the CONCATENATE operator, you can combine multiple cells and add other text or items in the expression.

In order to use the CONCATENATE operator, you will need to include it the formula each time you want to add something new to the expression.

To combine cells containing a first and last name by entering a formula in Excel using the CONCATENATE operator:

1. Select the worksheet that contains a first name column and a last name column. For example, first names may be in column A and last names may be in column B.
2. Click in the first cell where you want to insert a full name.
3. Type an equal sign (=).
4. Type the cell reference for the first cell you want to combine or click it.
5. Type the CONCATENATE operator (&) by pressing Shift + 7 (at the top of the keyboard).
6. Type the cell reference for the cell you want to combine or click it.
7. Repeat for other cells or items you want to add. If you want to add text, enter it in quotation marks or double quotes.
8. Press Enter.
9. Copy or fill the formula down by dragging the bottom right corner handle of the cell down to the cells where you want to copy the formula.

For example, if you wanted to combine the the first name and last name from cells A2 and B2, you could enter the following formula in C2:

=A2&B2

If you wanted to add a space between the data, you would enter the following formula in C2:

=A2&" "&B2

In the following example, we've entered a formula =A2&" "&B2 in C2 and then copied the formula down to the cells below by dragging the Fill handle on the bottom right corner of the cell:

2. Combine first and last name using the CONCATENATE function

You can also use the CONCATENATE function to combine cells containing first and last name into one cell using a formula. Using the CONCATENATE function, you can combine multiple cells and add other text or items in the expression.

With the CONCATENATE function, you simply include the items you want to combine in the arguments.

The Excel syntax for the CONCATENATE function is:

=CONCATENATE(text1, [text2], …)

The simple syntax for the CONCATENATE function is:

=CONCATENATE(item1, item2, item3, etc.)

Items could be spaces, commas, dashes, numbers, dates or other functions.

To combine cells containing a first and last name by entering a formula in Excel using the CONCATENATE function:

1. Select the worksheet that contains a first name column and a last name column. For example, first names may be in column A and last names may be in column B.
2. Click in the first cell where you want to insert a full name.
3. Type an equal sign (=).
4. Type CONCATENATE and an open round bracket or parentheses (.
5. Enter the first cell or item you want to combine (such as A2).
6. Type a comma (,) to separate the arguments.
7. Enter the next cell or item you want to combine (such as " ").
8. Type a comma.
9. Repeat for other cells or items you want to combine. If you want to add text, enter it in quotation marks or double quotes.
10. Type a closed round bracket or parentheses ).
11. Press Enter.
12. Copy or fill the formula down by dragging the bottom right corner handle of the cell down to the cells where you want to copy the formula.

For example, if you wanted to combine the first name and last name from cells A2 and B2, you could enter the following formula in C2:

=CONCATENATE(A2,B2)

If you wanted to add a space between the data, you could enter the following formula in C2:

=CONCATENATE(A2," ",B2)

If you wanted to add a comma between the data, you could enter the following formula in C2:

=CONCATENATE(B2,", ",A2)

In the following example, we've entered a formula =CONCATENATE(A2,", ",B2) in C2 and then copied the formula down to the cells below by dragging the Fill handle on the bottom right corner of the cell:

3. Combine first and last name using the CONCAT function

If you have Excel 2019 or a later version, you can also use the CONCAT function to combine cells containing first and last name. This function allows you to quickly combine two or more text strings together including a range of cells.

You can use the CONCAT function to combine cells in another cell using a formula. Using the CONCAT function, you can combine multiple cells and add other text or items in the expression.

The Excel syntax for the CONCAT function is:

=CONCAT(text1, [text2, … text_n], …)

The simple syntax for the CONCAT function is:

=CONCAT(item1, item2, item3, etc.) or =CONCAT(item1:itemN)

Items could be spaces, commas, dashes, numbers, dates or other functions.

In many cases, the CONCAT function behaves the same way as the CONCATENATE function. However, you can use ranges of cells with the CONCAT function.

For example:

=CONCAT(A2:C2)

To combine cells containing first and last name by entering a formula in Excel using the CONCAT function:

1. Select the worksheet that contains a first name column and a last name column. For example, first names may be in column A and last names may be in column B.
2. Click in the first cell where you want to insert a full name.
3. Type an equal sign (=).
4. Type CONCAT and an open round bracket or parentheses (.
5. Enter the first cell or item you want to combine (such as A2). Alternatively, you can enter a range of cells (such as A2:C2).
6. Type a comma (,) to separate the arguments.
7. Enter the next cell or item you want to combine (such as " ").
8. Type a comma.
9. Repeat for other cells or items you want to combine. If you want to add text, enter it in quotation marks or double quotes.
10. Type a closed round bracket or parentheses ).
11. Press Enter.
12. Copy or fill the formula down by dragging the bottom right corner handle of the cell down to the cells where you want to copy the formula.

For example, if you wanted to combine the data from cells A2 to B2, you could enter the following formula in C2:

=CONCAT(A2:B2)

If you wanted to add a dash between the data, you could enter the following formula in D2:

=CONCAT(A2,"-",B2)

In the following example, we've entered a formula =CONCAT(A2," ",B2) in C2 to combine first and last name and then filled down:

4. Combine first and last name using the TEXTJOIN function

If you have Excel 2021 or a later version, you can also use the TEXTJOIN function to combine cells containing first and last name. The TEXTJOIN function combines the text from multiple ranges and / or text strings and includes a delimiter (such as a space) you specify between each text value that will be combined. With TEXTJOIN, you can control the way Excel handles empty cells. This is useful if you have a column where middle initials are sometimes added.

The Excel syntax for the TEXTJOIN function is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

The simple syntax for the TEXTJOIN function is:

=TEXTJOIN(delimiter such as a space in quotation marks, TRUE to ignore empty cells, first item to combine, second item to combine, etc.)

You can also select a range of cells for the third argument.

Delimiters could be spaces, commas, dashes or some other character.

Enter FALSE or leave blank for the second argument if you want to include an empty cell.

To combine cells containing first and last name by entering a formula in Excel using the TEXTJOIN function:

1. Select the worksheet that contains a first name column and a last name column. There may also be a third column containing a middle initial.
2. Click in the first cell where you want to insert a full name.
3. Type an equal sign (=).
4. Type TEXTJOIN and an open round bracket or parentheses (.
5. Enter the required delimiter in quotation marks (such as " ").
6. Enter the first cell or item you want to combine (such as A2).
7. Type a comma (,) to separate the arguments.
8. Enter the next cell or item you want to combine (such as " ").
9. Type a comma.
10. Repeat for other cells or items you want to combine. If you want to add text, enter it in quotation marks or double quotes. Alternatively, you can simply drag over a range of cells.
11. Type a closed round bracket or parentheses ).
12. Press Enter.
13. Copy or fill the formula down by dragging the bottom right corner handle of the cell down to the cells where you want to copy the formula.

For example, if you wanted to combine the data from cells A2 to B2 and ignore blanks, you could enter the following in C2:

=TEXTJOIN(" ", TRUE, A2:B2) or =TEXTJOIN(" ", TRUE, A2, B2)

In the following example, we've entered a formula =TEXTJOIN(" ", TRUE, A2:C2) in D2 to combine first and last name as well as a middle initial but leave a blank if there is no middle initial and then copied the formula down:

The TEXTJOIN function is a great addition to the set of Excel functions when you have some cells that contain blanks.

5. Combine first and last name using Flash Fill

You can use Flash Fill in Excel to combine first name and last name without using formulas. In order to use Flash Fill, Excel must be able to detect a pattern in the data in a column or columns to the left of the column where you want to fill the data. The source data should be entered in a consistent way. Essentially, you need to enter an example of the data you want to combine in a cell to the right.

Flash Fill is available in Excel 2013 or a later version.

Unlike formulas, Flash Fill does not update the generated data so if the original data changes, you would need to repeat the Flash Fill process.

To use Flash Fill with a keyboard shortcut:

1. Select the worksheet that contains a first name column and a last name column. There may also be a third column containing a middle initial.
2. Click in the first cell to the right of one or more columns of data you want to combine (such as columns of first and last names).
3. Enter the example of the values you want to combine such as the first name and last name from cells in the same row in columns to the left. Flash Fill is case sensitive so enter the data in the desired case.
4. Press Enter.
5. In the cell below the example you entered, press Ctrl + E. Excel should generate combined values in the current column.

If you have columns of first names and last names, you can enter the example as last name, comma and then first name if you prefer.

In the following example, we've entered a full name in C2, with the last name first in capital letters, then a comma and then the first name in upper and lower case. We then pressed Enter and pressed Ctrl + E to Flash Fill:

You should check your data to be sure that Excel understands the pattern and fills correctly. If the data is inconsistent, enter 2 or 3 examples first and then in the next blank cell below, press Ctrl + E to Flash Fill.

The benefit of using formulas is if the data changes, the formula will update. Flash Fill does not. For more Flash Fill examples, check out 10 Flash Fill Examples.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Remove Duplicates in Excel (3 Easy Ways)

How to Lock Cells in Excel (Protect Formulas and Data)

How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

How to Insert Multiple Columns in Excel (4 Fast Ways with Shortcuts)

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query to Get and Transform Data

Microsoft Excel: New and Essential Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix^® Learning

You may also like

MORE EXCEL ARTICLES >

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca