Excel is a fantastic software for storing and analyzing data when you have to deal with text data like names, regions, departments, and product names.

It’s useful to know How to Combine the First and Last Names In Excel? Working with a collection of names is one of the most common tasks that most Excel users face.

You’ll frequently find that the first and last names are in distinct columns, and you’ll need to combine the first and last names to create a combined name in a cell.

In this article, we’ll teach you how to combine the first and last names in a variety of ways.

Ways to Combine the First and Last Name in Excel

Assume you have the following dataset and wish to merge the first name in column A with the last name in column B.

Using the CONCATENATE Function (or Ampersand)

Combining different text strings from different cells is quite easy in Excel. Excel has a Concatenate formula that is specifically designed for this purpose.

The formula for combining the first and last names (separated by a space character) is as follows:

=CONCAT (A2,” “,B2)

CONCAT is a new function in Excel 2016 that was designed to replace the CONCATENATE function. However, both features are still active for the time being, and you can use either.

If you want to utilize the CONCATENATE formula, it’s as follows:

=CONCATENATE (A2,” “,B2)

The formula above just adds the first and last names together. Choose” ” (space in double-quotes) as the second parameter because these should be separated by a space character.

Concatenation can also be done with the ampersand operator.

Assuming you have the same dataset, you can merge the first and last names using the formula below:

=A2&” “&B2

The text before and after the ampersand operator is combined. Merge three components in the preceding example: the initial name, a space character, and the last name.

You can combine the names in numerous formats now that you grasp the principle. You might want to put the last name first and then the first name or use a comma instead of a space between the two names.

Using the TEXTJOIN function

In Excel 2019 and Office 365, the TEXTJOIN function is accessible. If you have access to this function, you should use it to combine cells and columns (as it is far better than

the CONCATENATE and ampersand methods described above).

Assume you have the same dataset mentioned above and wish to combine the first and last names.

The formula for doing so is as follows:

=TEXTJOIN (” “,TRUE,A2:B2)

Three arguments are passed to the TEXTJOIN method above:

• In this case, the delimiter is a space character in double quotes (since we want the first and the last name to be separated by a space character)
• A Boolean value of TRUE indicates that if any blank cells exist, the formula will ignore them.
• The range in which you want to mix the cells.

It is both faster and easier to construct and handle than the standard concatenates formula. If you have access to Excel’s TEXTJOIN function, you should use it instead of any other formula.

Using the Flash Fill Method

Flash Fill is a clever feature that tries to figure out what the pattern is and then gives you the result.

Assume you have the same dataset described above and wish to combine the first and last names.

The steps to do this with Flash Fill are listed below.

1. In cell C2, type the desired outcome. It would be ‘Bobby Baker’ in our case.
2. Start typing the expected result in cell C3. You’ll see that Excel displays the projected outcome in each cell (in the light grey text). This is Flash Fill in action.
3. Click the Enter.

All of the cells in the table would be filled with the merged name as a result of the preceding procedures. It’s conceivable that you won’t observe flash fill while typing in the second cell in some instances.

Other steps to make Flash Fill work in this situation are listed below:

1. In cell C2, type the desired outcome. It would be ‘Bobby Baker’ in our case.
2. Choose C3.
3. Go to the Home tab.
4. Click the Fill icon in the Editing group.
5. In the ribbon, select the Flash Fill option.

The steps above would automatically select the pattern from the cell above and fill the full column with the merged name.

If Flash fill fails to recognize the correct pattern and produces an inaccurate result, manually fill two cells and then repeat the steps above. You can also use the Control + E keyboard shortcut to fill with Flash fill.

Combining the first and last names (or even the first, middle, and last names) is a straightforward procedure that Flash Fill can perform.

Flash Fill isn’t flawless, so keep that in mind. It operates by detecting patterns and applying the same pattern to all of the cells in a column. While the outcome of Flash Fill is most likely to perform as predicted, it’s a good idea to double-check it.

FAQs: How to Combine the First and Last Names In Excel?

How do I combine names in sheets?

Use the CONCATENATE feature in Google Sheets on the web as well as the mobile apps to merge text from two or more cells into a single cell.

This allows you to choose source cells as well as add text and separators before, between, and after the cells, you want to merge.

Why is Excel not copying and pasting correctly?

The size and form of the Copy and Paste areas are not the same. Before pasting, select the upper-left cell rather than the entire range. Click the cell where you wish the copied data’s upper-left cell to appear.

Conclusion

We have shown you how to combine the first and last names in Excel by using three different methods i.e. CONCATENATE Function, TEXTJOIN function, and Flash Fill.

However, if you know your first, middle, and last names, you can utilize the same procedures.

For more information about Excel visit our website.