Text › TEXTJOIN57 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeTEXTJOIN

TEXTJOIN: full address without double commas

The admin of an online uniform shop in Bandung is preparing shipping labels. Customer addresses are stored in four columns (street, RT/RW neighborhood unit, village, city), and not every customer filled in the RT/RW or village.

Task

Fill E2:E6 with the full address: columns A to D joined with a comma and a space (, ), skipping empty parts so no double commas are left. Use TEXTJOIN.

Cells to fillE2:E6

Required functionsTEXTJOIN

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
TEXTJOIN(delimiter, ignore_empty, text1, …) joins many cells at once with the same delimiter.
Hint 2
Set the second argument to TRUE so empty cells are skipped and no double commas appear.
Hint 3
The delimiter is ", " (a comma then a space), and the text can be a single range: columns A to D in the same row.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E2:E6

=TEXTJOIN(", ",TRUE,A2:D2)

Write it in E2, then fill down to E6.

Explanation

=TEXTJOIN(", ",TRUE,A2:D2) accepts a whole range, so you do not have to write A2&", "&B2&… piece by piece. With FALSE as the second argument, the Jl. Diponegoro row would become "Jl. Diponegoro No. 7, , Citarum, Bandung". CONCAT also accepts ranges, but it has no delimiter and cannot skip empty cells. Tip: the delimiter can be CHAR(10) (a line break) for multi-line address labels.

Starting worksheet: Sheet1
Row	A	B	C	D	E
1	Street	RT/RW	Village	City	Full address
2	Jl. Cihampelas No. 12	RT 03/RW 05	Cipaganti	Bandung
3	Jl. Diponegoro No. 7		Citarum	Bandung
4	Perum Griya Asri Blok C2	RT 01/RW 09		Cimahi
5	Jl. Merdeka No. 45			Garut
6	Jl. Braga No. 8	RT 02/RW 01	Braga	Bandung
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result