Blog

The CONCAT function is a widely-used text function in Excel for concatenating text strings. In this tutorial, you’ll learn its structure, applications, and practical examples. This content is ideal for those managing corporate or organizational reports/forms in Excel.

Estimated reading time: 7 minutes

Inputs for Excel’s CONCAT Function

The CONCAT function accepts 2 to 254 text arguments. These can include:

• Cell references (e.g., A1, B2)
• Static text like “ID Number: “
• Results of other functions (e.g., TEXT(), TODAY())
• Special characters like space " ", line break CHAR(10), etc.

Arguments for Excel’s CONCAT function are shown in the Function Arguments dialog below:

Note that Excel’s CONCAT function can accept up to 254 different arguments, meaning it can concatenate 254 text items.

Syntax of Excel’s CONCAT Function

=CONCAT(Text1, Text2, ..., Text254)

You can also input an entire range:

=CONCAT(A1:A5)

If you need delimiters between values (like commas or spaces), the newer TEXTJOIN function is better suited.

Practical CONCAT Examples

Example 1: Combining Personal Information for Government Forms

Suppose an employment form contains:

• A2: First Name
• B2: Last Name
• C2: National ID

Create a complete sentence in column D:

=CONCAT("Full Name: ", A2, " ", B2, " - ID: ", C2)

Example 2: Generating Transaction IDs for Bank Transfers

In a banking system with:

• A2: Customer ID (e.g., 8721)
• B2: Date (e.g., 2024/05/01)
• C2: Branch Code (e.g., 205)

Generate a composite tracking code:

=CONCAT("TX-", A2, "-", TEXT(B2,"yyyymmdd"), "-", C2)

Output: TX-8721-20240501-205

Example 3: Formatting Postal Addresses in Sales Reports

For distribution companies needing complete addresses:

• A2: State
• B2: City
• C2: Street
• D2: Building No.

Combined formula:

=CONCAT("Address: ", A2, ", ", B2, ", ", C2, ", No. ", D2)

Completing Arguments in Function Arguments Dialog

When using Insert Function or typing CONCAT in the formula bar:

1. Type cell references or text manually
2. Use cell ranges
3. Nest other functions as arguments

All options can be done with your mouse – no manual typing required.

Frequently Asked Questions

1. What’s the difference between CONCAT and &?

Both combine text, but CONCAT is more readable, professional, and easier for long text/ranges:

=CONCAT(A1, B1)

Equivalent to:

=A1 & B1

2. Why doesn’t CONCAT add spaces between items in range A1:A5?

CONCAT doesn’t include separators by default. Use TEXTJOIN instead:

=TEXTJOIN(" - ", TRUE, A1:A5)

3. Is CONCAT available in older Excel versions?

No, CONCAT was added in Excel 2016+. Use CONCATENATE or & in older versions.

For additional questions, I’m happy to help.
Explore more practical Excel tutorials in our Advanced Excel Guides section.