CONCATENATE and JOIN in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this JOIN formula?

Given the cells A1=Apple, A2=Banana, A3=Cherry, what is the result of the formula =JOIN(", ", A1:A3)?

Google Sheets

=JOIN(", ", A1:A3)

A Apple, Banana, Cherry

B AppleBananaCherry

C Apple; Banana; Cherry

D Apple, Banana; Cherry

💡 Hint

JOIN combines text with a separator you choose.

JOIN uses the first argument as a separator between all values in the range. Here, ", " separates the fruits.

📊 Formula Result

intermediate

What does this CONCATENATE formula produce?

If B1 contains "Hello" and B2 contains "World", what is the output of =CONCATENATE(B1, " ", B2)?

Google Sheets

=CONCATENATE(B1, " ", B2)

A Hello World

B Hello-World

C HelloWorld

D Hello, World

💡 Hint

CONCATENATE joins text exactly as you list it.

The formula joins B1, a space, then B2, resulting in "Hello World".

❓ Function Choice

advanced

Which formula correctly joins cells A1 to A4 with a dash (-) between them?

Choose the formula that will produce the text: "Red-Green-Blue-Yellow" from cells A1=Red, A2=Green, A3=Blue, A4=Yellow.

A =JOIN(A1:A4, "-")

B =TEXTJOIN("-", TRUE, A1:A4)

C =JOIN("-", A1:A4)

D =CONCATENATE(A1, "-", A2, "-", A3, "-", A4)

💡 Hint

JOIN takes the separator first, then the range.

Option B uses TEXTJOIN which correctly joins the range with a dash separator. Option B uses JOIN with correct syntax but TEXTJOIN is more flexible. Option B works but is longer. Option B has wrong argument order.

🎯 Scenario

advanced

You want to combine first and last names from columns A and B with a space between. Which formula is best?

Columns A and B contain first and last names respectively. You want full names like "John Smith" in column C. Which formula in C1 will do this correctly?

A =TEXTJOIN("", TRUE, A1, B1)

B =JOIN(" ", A1:B1)

C =A1 & " " & B1

D =CONCATENATE(A1, " ", B1)

💡 Hint

CONCATENATE and & both join text, but JOIN needs a range.

Option D correctly joins first and last name with a space. Option D joins A1 and B1 but JOIN expects a range, A1:B1 is a range and JOIN will join both cells with space separator, so it also works. Option D uses & which also works but question asks for best formula using CONCATENATE or JOIN. Option D joins without space.

❓ data_analysis

expert

How many items does this JOIN formula combine?

Given the range A1:A5 contains {"Dog", "Cat", "", "Fish", "Bird"}, what is the number of items combined by =JOIN(",", A1:A5)?

Google Sheets

=JOIN(",", A1:A5)

A 3

B 4

C 5

D Depends on non-empty cells only

💡 Hint

JOIN includes empty cells as empty strings.

JOIN combines all cells in the range, including empty ones, so it combines 5 items, inserting separators between all.

Practice

1. What does the CONCATENATE function do in Google Sheets?

easy

A. Joins multiple text pieces exactly as listed without any separator.

B. Adds numbers together in a range.

C. Sorts text alphabetically.

D. Splits text into separate cells.

Solution

1. Step 1: Understand CONCATENATE purpose

   CONCATENATE joins text pieces exactly as you list them, without adding anything extra.

2. Step 2: Compare with other options

   Options A, B, and C describe different functions unrelated to CONCATENATE.

3. Final Answer:

   Joins multiple text pieces exactly as listed without any separator. -> Option A

4. Quick Check:

   CONCATENATE joins text pieces exactly [OK]

Hint: CONCATENATE sticks texts together as-is, no separator [OK]

Common Mistakes:

• Thinking CONCATENATE adds spaces automatically
• Confusing CONCATENATE with SUM or SPLIT
• Assuming CONCATENATE sorts text

2. Which of these is the correct syntax to join cells A1, B1, and C1 using CONCATENATE in Google Sheets?

easy

A. =CONCATENATE(A1, B1, C1)

B. =CONCATENATE(A1; B1; C1)

C. =CONCATENATE(A1 B1 C1)

D. =CONCATENATE(A1+B1+C1)

Solution

1. Step 1: Recall CONCATENATE syntax

   CONCATENATE uses commas to separate arguments, like =CONCATENATE(A1, B1, C1).

2. Step 2: Check each option

   =CONCATENATE(A1; B1; C1) uses semicolons which is incorrect in Google Sheets. =CONCATENATE(A1 B1 C1) misses commas. =CONCATENATE(A1+B1+C1) uses plus signs which is invalid for CONCATENATE.

3. Final Answer:

   =CONCATENATE(A1, B1, C1) -> Option A

4. Quick Check:

   CONCATENATE uses commas between arguments [OK]

Hint: Use commas to separate items inside CONCATENATE [OK]

Common Mistakes:

• Using semicolons instead of commas
• Omitting commas between arguments
• Using plus signs inside CONCATENATE

3. Given cells A1=John, B1=Doe, and C1=2024, what is the result of =JOIN("-", A1, B1, C1)?

medium

A. JohnDoe2024

B. John-Doe-2024

C. John, Doe, 2024

D. John Doe 2024

Solution

1. Step 1: Understand JOIN function

   JOIN combines items with the separator you provide. Here, the separator is a dash "-".

2. Step 2: Apply JOIN to given cells

   It joins "John", "Doe", and "2024" with dashes: "John-Doe-2024".

3. Final Answer:

   John-Doe-2024 -> Option B

4. Quick Check:

   JOIN with "-" adds dashes between items [OK]

Hint: JOIN adds your separator between all items [OK]

Common Mistakes:

• Forgetting the separator in JOIN
• Confusing JOIN with CONCATENATE (no separator)
• Using commas instead of dashes

4. You want to join the range A1:A3 with commas using JOIN, but your formula =JOIN(A1:A3, ",") gives an error. What is the fix?

medium

A. Remove the comma separator

B. Use CONCATENATE instead of JOIN

C. Change formula to =JOIN(",", A1:A3)

D. Use semicolons instead of commas

Solution

1. Step 1: Check JOIN syntax

   JOIN syntax is JOIN(separator, range). The separator must be first.

2. Step 2: Fix the formula

   Switch arguments to =JOIN(",", A1:A3) to avoid error.

3. Final Answer:

   =JOIN(",", A1:A3) -> Option C

4. Quick Check:

   JOIN(separator, range) correct order [OK]

Hint: Put separator first in JOIN function [OK]

Common Mistakes:

• Putting range before separator in JOIN
• Using CONCATENATE for ranges
• Removing separator argument

5. You have a list of names in cells A1:A4: Alice, Bob, , and Eve (A3 is empty). Which formula correctly joins only the non-empty names with a semicolon using JOIN?

hard

A. =JOIN(";", IF(A1:A4<>"", A1:A4))

B. =JOIN(";", A1:A4)

C. =CONCATENATE(A1:A4)

D. =JOIN(";", FILTER(A1:A4, A1:A4<>""))

Solution

1. Step 1: Understand the problem

   We want to join only non-empty cells with semicolons.

2. Step 2: Analyze options

   =JOIN(";", A1:A4) joins all including empty, resulting in extra separators. =JOIN(";", FILTER(A1:A4, A1:A4<>"")) uses FILTER to exclude empty cells before joining. =CONCATENATE(A1:A4) uses CONCATENATE which does not add separators. =JOIN(";", IF(A1:A4<>"", A1:A4)) uses IF incorrectly without ARRAYFORMULA, causing errors.

3. Final Answer:

   =JOIN(";", FILTER(A1:A4, A1:A4<>"")) -> Option D

4. Quick Check:

   FILTER removes empty cells before JOIN [OK]

Hint: Use FILTER to exclude blanks before JOIN [OK]

Common Mistakes:

• Joining ranges with empty cells directly
• Using CONCATENATE instead of JOIN for lists
• Incorrect use of IF without ARRAYFORMULA