CONCATENATE and CONCAT in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that combines customer first and last names into full names and also creates a unique order ID by joining order number and customer ID.

📊 Data: You have a table with columns: Order Number, Customer ID, First Name, Last Name, and Product.

🎯 Deliverable: Create two new columns: Full Name (combining first and last names) and Unique Order ID (combining order number and customer ID).

Sample Data

Order Number	Customer ID	First Name	Last Name	Product
1001	C001	John	Doe	Shirt
1002	C002	Jane	Smith	Pants
1003	C003	Emily	Jones	Hat
1004	C004	Michael	Brown	Shoes
1005	C005	Linda	Wilson	Jacket
1006	C006	David	Lee	Gloves
1007	C007	Sarah	Kim	Scarf
1008	C008	Robert	Clark	Belt

1

Step 1: Create a new column named 'Full Name' next to the 'Last Name' column.

In the first cell under 'Full Name' (e.g., E2), enter the formula: =CONCATENATE(C2, " ", D2)

Expected Result

For row 2, the cell shows 'John Doe'

2

Step 2: Copy the 'Full Name' formula down for all rows to combine first and last names.

Drag the fill handle from E2 down to E9 to apply =CONCATENATE(C3, " ", D3) and so on.

Expected Result

Row 3 shows 'Jane Smith', row 4 shows 'Emily Jones', etc.

3

Step 3: Create another new column named 'Unique Order ID' after the 'Full Name' column.

In the first cell under 'Unique Order ID' (e.g., F2), enter the formula: =CONCAT(B2, A2)

Expected Result

For row 2, the cell shows 'C0011001'

4

Step 4: Copy the 'Unique Order ID' formula down for all rows to combine customer ID and order number.

Drag the fill handle from F2 down to F9 to apply =CONCAT(B3, A3) and so on.

Expected Result

Row 3 shows 'C0021002', row 4 shows 'C0031003', etc.

Final Result

Order Number | Customer ID | First Name | Last Name | Full Name    | Unique Order ID
-------------------------------------------------------------------------------------
1001         | C001        | John       | Doe       | John Doe    | C0011001
1002         | C002        | Jane       | Smith     | Jane Smith  | C0021002
1003         | C003        | Emily      | Jones     | Emily Jones | C0031003
1004         | C004        | Michael    | Brown     | Michael Brown | C0041004
1005         | C005        | Linda      | Wilson    | Linda Wilson | C0051005
1006         | C006        | David      | Lee       | David Lee   | C0061006
1007         | C007        | Sarah      | Kim       | Sarah Kim   | C0071007
1008         | C008        | Robert     | Clark     | Robert Clark | C0081008

✓Full Name column correctly combines first and last names with a space.

✓Unique Order ID column combines Customer ID and Order Number without spaces.

✓CONCATENATE and CONCAT functions are both useful for joining text in Excel.

Bonus Challenge

Modify the Unique Order ID to include a dash between Customer ID and Order Number, like 'C001-1001'.

Show Hint

Use CONCAT or CONCATENATE with "-" between the two values, for example: =CONCAT(B2, "-", A2)

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