Skip to main content
/2 min read

Using Concatenate

Joining Text in Excel

& Operator

=A1 & " " & B1 — most common, concise way to join.

CONCAT()

Modern replacement for CONCATENATE. Handles ranges.

TEXTJOIN()

Adds a delimiter between values, optionally skips blanks. =TEXTJOIN(", ", TRUE, A1:A10).

Formatting Numbers

Wrap numbers in TEXT() to keep formatting: TEXT(B1, "$#,##0").

Master Excel at Noble Desktop

Noble Desktop's Excel Bootcamp covers formulas, pivot tables, data analysis, and VBA.

Learn how to use CONCATENATE.

Using CONCATENATE

The word CONCATENATE means to combine, to merge two or more things into one thing.

In Excel, we use this function to take the contents of two or more separate cells and combine their contents into another cell, leaving the original cells intact.

The simplest – and probably most common – use for this is making a Full Name from separate First and Last Names in a worksheet.

Using this list of employee names and other info, I’ve added a column for their full names, and I’ll create the values in that column for each person in the list, using the CONCATENATE function to combine the contents of the First Name and Last Name cells.

I’ll do it for the first person, and then using Paste Fill, complete the Full Name values for the remaining employees.

So let’s get started. To invoke this function in cell D4, I type:

=CONCATENATE

And then I press the Tab key to insert the opening parenthesis for me – or I could type it manually.

Now let’s look at the arguments involved in this function.

First, we have Text1, then Text 2, and then an ellipsis, indicating that you can continue adding text items. These arguments refer to cell addresses in this or another worksheet and/or to words or numbers you type inside quotes. We’ll be using both to create a series of three arguments.

So, I’ll click in cell C4, the First Name cell for the first record, and then type a comma.

And then in quotes, I’ll type a space – this puts the space between the first name and last name. I follow this with another comma, to continue separating my arguments.

Finally, I click in cell B4, where the Last Name lives, and that will add the third argument. When I close the parentheses and press ENTER, you can see my first concatenated name!

Then, to repeat this for the whole list, I use the Fill handle and drag down through the remaining employees.

Pretty simple, and powerful!