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.