CONCATENATE and CONCAT in Excel - Step-by-Step Guide

Introduction

These functions join text from different cells into one cell. CONCATENATE is the older way, and CONCAT is the newer, simpler way to combine text pieces.

When you want to combine first and last names from two columns into one full name column.

When you need to join a product code and description into a single cell for easier reading.

When creating a full address by joining street, city, and postal code from separate cells.

When you want to add a label or unit after a number, like adding 'kg' after a weight value.

When merging date parts like day, month, and year from different cells into one date string.

Steps

Step 1: Click

- cell where you want the combined text to appear

The cell is selected and ready for input

Step 2: Type

- formula bar

You start entering the formula

💡 Start with =CONCATENATE( or =CONCAT(

Step 3: Select

- cells you want to join, separated by commas

The formula includes references to the selected cells

💡 For example, =CONCATENATE(A1, B1) or =CONCAT(A1, B1)

Step 4: Type

- formula bar

Complete the formula with closing parenthesis )

💡 You can add text inside quotes, like =CONCATENATE(A1, " ", B1) to add a space

Step 5: Press

- Enter key

The combined text appears in the selected cell

Before vs After

Before

Cell A1 has 'John', Cell B1 has 'Doe'

After

Cell C1 shows 'JohnDoe' with =CONCATENATE(A1, B1) or 'John Doe' with =CONCATENATE(A1, " ", B1)

Settings Reference

Function arguments

📍 Formula bar when typing CONCATENATE or CONCAT

Defines which pieces of text or cell values to combine

Default: No default, you must specify what to join

Text separator

📍 Inside the formula as text in quotes

Adds spaces or punctuation between joined text parts

Default: No separator unless added manually

Common Mistakes

Forgetting to add spaces between words

The joined text runs together without spaces, making it hard to read

Add a space as a text argument in quotes, like =CONCATENATE(A1, " ", B1)

Using CONCATENATE with too many arguments

CONCATENATE can only handle up to 255 arguments, which can be limiting

Use CONCAT instead, which can handle ranges and more arguments

Typing text without quotes inside the formula

Excel treats unquoted text as cell references and shows errors if they don't exist

Always put text strings inside double quotes, like "Hello"

Summary

CONCATENATE and CONCAT join text from multiple cells into one cell.

CONCAT is the newer, simpler function that can handle ranges and more arguments.

Remember to add spaces or punctuation inside quotes to separate joined text.

Practice

1. What does the CONCATENATE function do in Excel?

easy

A. Calculates the sum of numbers in cells

B. Joins text from multiple cells into one text string

C. Finds the average of numbers in cells

D. Sorts data alphabetically

Solution

1. Step 1: Understand CONCATENATE function purpose

   The CONCATENATE function joins text from different cells into one continuous text string.

2. Step 2: Compare with other options

   Options A, C, and D describe different functions unrelated to joining text.

3. Final Answer:

   Joins text from multiple cells into one text string -> Option B

4. Quick Check:

   CONCATENATE joins text = Joins text from multiple cells into one text string [OK]

Hint: CONCATENATE joins text pieces into one string [OK]

Common Mistakes:

• Thinking CONCATENATE sums numbers
• Confusing CONCATENATE with sorting functions
• Assuming CONCATENATE finds averages

2. Which of the following is the correct syntax to join cells A1 and B1 with a space using CONCAT?

easy

A. =CONCAT(A1, " ", B1)

B. =CONCAT(A1 + " " + B1)

C. =CONCATENATE(A1 B1)

D. =CONCAT(A1 & B1)

Solution

1. Step 1: Review CONCAT syntax

   CONCAT joins text items separated by commas; to add space, include " " as an argument.

2. Step 2: Check each option

   =CONCAT(A1, " ", B1) correctly uses commas and includes space as " ". Others use incorrect operators or missing commas.

3. Final Answer:

   =CONCAT(A1, " ", B1) -> Option A

4. Quick Check:

   CONCAT uses commas and quotes for spaces = =CONCAT(A1, " ", B1) [OK]

Hint: Use commas and " " for spaces inside CONCAT [OK]

Common Mistakes:

• Using plus (+) instead of commas in CONCAT
• Missing commas between arguments
• Using CONCATENATE syntax with CONCAT incorrectly

3. Given cells: A1 = "Good", B1 = "Morning", what is the result of =CONCATENATE(A1, " ", B1)?

medium

A. Good Morning

B. Good+Morning

C. Good, Morning

D. GoodMorning

Solution

1. Step 1: Understand CONCATENATE with spaces

   The formula joins A1 and B1 with a space " " between them.

2. Step 2: Combine the texts

   "Good" + " " + "Morning" results in "Good Morning" with a space.

3. Final Answer:

   Good Morning -> Option A

4. Quick Check:

   CONCATENATE adds space = "Good Morning" [OK]

Hint: Add " " inside CONCATENATE for spaces [OK]

Common Mistakes:

• Forgetting to add space argument
• Expecting comma or plus signs in output
• Confusing CONCATENATE with automatic spacing

4. Identify the error in this formula: =CONCATENATE(A1 B1) to join cells A1 and B1.

medium

A. Extra parentheses used

B. CONCATENATE cannot join cells

C. Missing comma between A1 and B1

D. Spaces are not allowed in CONCATENATE

Solution

1. Step 1: Check CONCATENATE argument syntax

   Arguments must be separated by commas; here A1 and B1 are missing a comma.

2. Step 2: Confirm other options

   CONCATENATE can join cells, parentheses are correct, spaces are allowed inside strings.

3. Final Answer:

   Missing comma between A1 and B1 -> Option C

4. Quick Check:

   Arguments need commas = Missing comma between A1 and B1 [OK]

Hint: Separate CONCATENATE arguments with commas [OK]

Common Mistakes:

• Omitting commas between arguments
• Thinking CONCATENATE can't join cells
• Misplacing parentheses

5. You have cells A1:A3 with values "Red", "Green", "Blue". Which formula using CONCAT correctly joins them with commas and spaces to get "Red, Green, Blue"?

hard

A. =CONCAT(A1:A3)

B. =CONCATENATE(A1 & ", " & A2 & ", " & A3)

C. =CONCAT(A1:A3, ", ")

D. =CONCAT(A1, ", ", A2, ", ", A3)

Solution

1. Step 1: Understand CONCAT with multiple arguments

   CONCAT joins each argument in order; to add commas and spaces, include them as separate text arguments.

2. Step 2: Evaluate each option

   =CONCAT(A1, ", ", A2, ", ", A3) correctly joins A1, ", ", A2, ", ", A3 producing "Red, Green, Blue". =CONCATENATE(A1 & ", " & A2 & ", " & A3) uses CONCATENATE with & which works but is not CONCAT. =CONCAT(A1:A3, ", ") incorrectly tries to add separator to a range. =CONCAT(A1:A3) joins without commas.

3. Final Answer:

   =CONCAT(A1, ", ", A2, ", ", A3) -> Option D

4. Quick Check:

   CONCAT with commas as text = =CONCAT(A1, ", ", A2, ", ", A3) [OK]

Hint: Add commas as separate text arguments in CONCAT [OK]

Common Mistakes:

• Using range with CONCAT expecting separators
• Confusing CONCAT with CONCATENATE syntax
• Forgetting to add commas and spaces manually