How to Use the CONCATENATE Function in Excel

The CONCATENATE function in Excel combines text from multiple cells into a single cell. It is especially useful when working with names, addresses, IDs, product descriptions, or any situation where data is split across multiple columns and needs to be merged.

Although CONCATENATE is considered a legacy function and has been replaced by CONCAT in newer versions of Excel, many older spreadsheets still use it. Understanding how CONCATENATE works can help you maintain existing workbooks and work more efficiently with text data.

In This Guide, You’ll Learn

• What the CONCATENATE function does
• How to write the CONCATENATE formula correctly
• How to combine text, numbers, and spaces
• Practical business examples using CONCATENATE
• Common CONCATENATE mistakes to avoid
• Why CONCAT replaced CONCATENATE in newer Excel versions

What is the CONCATENATE Function?

The CONCATENATE function combines text from multiple cells, numbers, or text strings into a single text value.

For example, if a first name is stored in one column and a last name is stored in another, CONCATENATE can combine them into a full name.

CONCATENATE Function Syntax

=CONCATENATE(text1, [text2], ...)

Argument Definitions

• text1 – The first text item to combine (required)
• text2 – Additional text items to combine (optional)
• You can combine text, numbers, cell references, or text strings inside quotation marks
• Excel allows multiple arguments in a single CONCATENATE formula

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> CONCATENATE Function Data

Formulas Used

=CONCATENATE(A2," ",B2)

=CONCATENATE(A3," ",B3)

=CONCATENATE(A4," ",B4)

=CONCATENATE(A5," ",B5)

How This Formula Works

• Excel takes the text in cell A2
• Adds a space (” “)
• And adds the text in cell B2

Create Employee IDs

CONCATENATE is useful for creating employee IDs or account numbers using text and numbers together.

Formulas Used

=CONCATENATE(A2,"-",B2)

=CONCATENATE(A3,"-",B3)

=CONCATENATE(A4,"-",B4)

=CONCATENATE(A5,"-",B5)

How This Formula Works

• Excel takes the text in cell A2
• Adds a hyphen within the quotes (“-“)
• And adds the numbers in cell B2

Build Email Addresses

A real-world business use of CONCATENATE is generating company email addresses from employee names.

Formulas Used

=CONCATENATE(A2,".",B2,"@company.com")

=CONCATENATE(A3,".",B3,"@company.com")

=CONCATENATE(A4,".",B4,"@company.com")

=CONCATENATE(A5,".",B5,"@company.com")

How This Formula Works

• Excel takes the text in cell A2
• Adds a period within the quotes (“.”)
• Adds the text in cell B2
• And adds the company name within the quotes (“@company.com”)

This is especially useful for HR departments and employee onboarding processes.

Combine Address Fields

Businesses often store address components in separate columns. CONCATENATE can combine them into one mailing address.

Formulas Used

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

=CONCATENATE(A3,", ",B3,", ",C3)

=CONCATENATE(A4,", ",B4,", ",C4)

=CONCATENATE(A5,", ",B5,", ",C5)

How This Formula Works

• Excel takes the text in cell A2
• Adds a space within the quotes (” “)
• Adds the text in cell B2
• Adds a space within the quotes (” “)
• And adds the text in cell C2

Common CONCATENATE Mistakes

Forgetting Spaces

This formula:

=CONCATENATE(A2,B2)

Returns:

JohnSmith

Instead, include a space:

=CONCATENATE(A2," ",B2)

Forgetting Quotation Marks

Text separators like spaces, commas, or hyphens must be placed inside quotation marks.

Correct:

"-"

Incorrect:

-

Using CONCATENATE in Newer Workbooks

Excel still supports CONCATENATE, but Microsoft recommends using:

=CONCAT()

or

=TEXTJOIN()

for newer spreadsheets.

Why CONCAT Replaced CONCATENATE

Microsoft introduced CONCAT as a newer and more flexible version of CONCATENATE.

Key differences:

• CONCATENATE is a legacy function
• CONCAT can combine cell ranges more easily
• Both functions still work in Excel

If you are maintaining older spreadsheets, knowing CONCATENATE remains valuable.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> CONCATENATE Function Data

Related Articles