How to Create a CONCATENATE Function in Excel

I remember the first time I needed to merge first names and last names from two separate columns into one “Full Name” column for a mail merge. My initial approach was retyping every single name by hand — for over 500 rows. A colleague stopped me and said, “Just use CONCATENATE.” That one function turned an hour of tedious retyping into a formula I dragged down in about ten seconds. In this article, I’ll show you everything you need to know about combining text in Excel, from the classic CONCATENATE function to its modern replacements.

What Is the CONCATENATE Function?

CONCATENATE is an Excel function that joins two or more pieces of text together into a single cell. It can combine text from different cells, literal text you type directly, numbers, dates, and even the results of other formulas — all merged into one continuous string.

The basic syntax is:

=CONCATENATE(text1, text2, text3, ...)

Note: In newer versions of Excel (2016 and later, and Excel 365), Microsoft introduced CONCAT and TEXTJOIN as more flexible replacements, though CONCATENATE still works for backward compatibility.

Why Use CONCATENATE?

• It combines data from multiple columns into one, cleanly formatted cell.
• It’s essential for creating full names, full addresses, or combined ID codes.
• It helps prepare data for mail merges, labels, and reports.
• It can mix cell references with custom text and punctuation.
• It reduces manual retyping and copy-paste errors.

Required Data Before You Start

To use CONCATENATE effectively, you need:

1. Two or more source cells (or pieces of text) that you want to combine.
2. A clear idea of what separators you need between them — spaces, commas, dashes, or nothing at all.
3. An empty destination cell where the combined result will appear.

Step-by-Step: How to Use CONCATENATE

Step 1: Click the Destination Cell

Select the empty cell where you want the combined text to appear.

Step 2: Start the Formula

Type:

=CONCATENATE(

Step 3: Add Your First Reference

Click the first cell you want to include, or type it manually, like A2.

Step 4: Add a Separator (If Needed)

To include a space between two pieces of text, add " " (a space inside quotation marks) as the next argument.

Step 5: Add the Next Reference

Click or type the next cell, like B2.

Step 6: Close the Formula

Type the closing parenthesis and press Enter:

=CONCATENATE(A2," ",B2)

Step 7: Copy the Formula Down

Use the fill handle to drag the formula down through the rest of your rows, and Excel automatically adjusts the cell references for each row.

Practical Example

Suppose column A has first names and column B has last names:

You can also combine text with punctuation and static labels. For example, to build a formatted address line:

=CONCATENATE(A2, ", ", B2, ", ", C2, " ", D2)

This might combine street, city, state, and zip code into a single formatted address like “123 Main St, Springfield, IL 62704.”

You can also mix in literal text for labels:

=CONCATENATE("Invoice #: ", A2)

This produces something like “Invoice #: 10023” by combining a text label with a cell value.

Modern Alternatives: CONCAT and TEXTJOIN

If you’re using Excel 2016, 2019, or Excel 365, consider these newer functions instead:

CONCAT

Works almost identically to CONCATENATE but supports ranges directly:

=CONCAT(A2:D2)

This combines all four cells without needing to list each one individually.

TEXTJOIN

The most powerful option — it lets you specify a delimiter once and ignore empty cells automatically:

=TEXTJOIN(", ", TRUE, A2:D2)

This joins A2 through D2 with a comma and space between each value, and the TRUE argument tells Excel to skip any blank cells in the range — something CONCATENATE and CONCAT can’t do automatically.

I generally recommend TEXTJOIN for any modern spreadsheet, since it handles separators and blank cells far more gracefully than the older CONCATENATE function.

Using the “&” Operator as a Shortcut

Instead of typing out the full CONCATENATE function, many people use the ampersand (&) operator, which does the exact same thing:

=A2&" "&B2

This produces the identical result as =CONCATENATE(A2," ",B2) but with less typing. It’s worth knowing both methods, since you’ll encounter the & shortcut frequently in other people’s spreadsheets.

Common Mistakes to Avoid

1. Forgetting the space or separator – without " " between two text arguments, “John” and “Smith” become “JohnSmith” instead of “John Smith.”
2. Combining numbers without formatting – CONCATENATE converts numbers to plain text, which can strip formatting like currency symbols or leading zeros. Use the TEXT function to control formatting first, like =CONCATENATE("$",TEXT(A2,"#,##0.00")).
3. Using CONCATENATE with entire ranges – the classic CONCATENATE function doesn’t support ranges directly (you’d have to list every cell individually); use CONCAT or TEXTJOIN instead for ranges.
4. Not accounting for blank cells – CONCATENATE will still add separators even for blank cells, resulting in awkward double spaces or dangling commas. TEXTJOIN with the “ignore empty” option solves this.
5. Overwriting original data by mistake – always build the combined result in a new column, not by overwriting your original first/last name columns, in case you need the separate values later.

Troubleshooting Tips

• Formula shows the formula text instead of a result? Check that the cell isn’t formatted as “Text” — change the cell format to “General” and re-enter the formula.
• Numbers look wrong after combining? Wrap number references in the TEXT function to control decimal places, currency symbols, or date formatting before combining.
• Getting double spaces or extra commas? This usually means a source cell is blank; switch to TEXTJOIN with the ignore-empty option to handle this automatically.
• Formula works but doesn’t update when source cells change? Confirm you’re referencing cells (like A2) rather than typing static values directly into the formula.

Real-World Use Cases

• Mail merges – combining first and last names, or full mailing addresses, for letters and labels.
• ID or code generation – combining department codes, dates, and sequential numbers into unique identifiers.
• Report labels – building dynamic titles like “Sales Report – March 2025” by combining static text with a date cell.
• Data cleanup – merging split data (like separate area code and phone number columns) back into a single, standard format.
• Dashboard summaries – creating dynamic sentence-style summaries, like “Revenue increased by 12% this quarter,” by combining formula results with descriptive text.

Best Practices

• Use TEXTJOIN or CONCAT instead of the older CONCATENATE function whenever your Excel version supports it.
• Always wrap numbers and dates in the TEXT function first if you need specific formatting in the combined output.
• Keep your combined formulas in a new column rather than overwriting original source data.
• Use clear, consistent separators (commas, spaces, dashes) to keep combined text readable.
• Test your formula on a few rows with edge cases (blank cells, unusual characters) before applying it to the entire dataset.

Final Thoughts

Whether you stick with the classic CONCATENATE function or upgrade to CONCAT and TEXTJOIN, combining text in Excel is a skill you’ll use constantly — from building full names to formatting reports to generating unique IDs. Once you get comfortable mixing cell references, separators, and literal text, you’ll find yourself relying on this function in almost every spreadsheet you build.