Excel Challenge 5: Merge Multiple Columns into One

Challenge Scenario

In online stores and logistics fulfillment centers, delivery addresses are often stored across separate columns: Street Address, Suite/Unit, City, and State. When printing shipping labels, these fields must be merged into one single clean line with commas (such as "123 Main St, Suite 400, Seattle, WA").

The challenge with traditional ampersand concatenation (&) is that single-family houses lack an apartment or suite number. Joining empty cells with commas produces awkward double commas like "456 Oak Ave, , Portland, OR".

As a Shipping & Logistics Assistant, your task is to join these address fields using TEXTJOIN so blank suite cells are automatically ignored, and audit your address dataset:

Mailing Address (Column E): Join columns A to D with a comma and space (", ") and ignore empty cells using =TEXTJOIN(", ", TRUE, A2:D2).
Total Labels (Cell B8): Count total completed mailing labels using COUNTA(E2:E5).
Units / Suites Count (Cell C8): Count how many addresses have unit or apartment numbers using COUNTA(B2:B5).
Standalone Homes Count (Cell D8): Count how many addresses lack suite or apartment numbers using COUNTBLANK(B2:B5).

Your Sheet Layout

Before writing formulas, let's understand the structure of this shipping worksheet:

Source Address Columns (Columns A to D): The separate address components (Street, Suite, City, State). Note that cells B3 and B5 are intentionally empty.
Merged Mailing Label (Column E): The target column where your TEXTJOIN formula creates the formatted 1-line mailing address.
Address Inventory Audit (Rows 7 & 8): Bottom audit row where you verify total label volume and apartment vs standalone residence counts.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:D5`	Street, Suite, City, State	Source Addresses	Original raw address components	Separate address pieces. Note that cells B3 and B5 are blank.
`E2:E5`	Mailing Address	Smart Merge	`=TEXTJOIN(", ", TRUE, A2:D2)`	Combines all 4 columns into one line, automatically skipping blank suite cells.
`B8`	Total Labels	Audit Summary	`=COUNTA(E2:E5)`	Counts total number of mailing labels created (4).
`C8`	Units / Suites	Audit Summary	`=COUNTA(B2:B5)`	Counts addresses with apartment or suite numbers (2).
`D8`	Standalone Homes	Audit Summary	`=COUNTBLANK(B2:B5)`	Counts residential addresses without suite numbers (2).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: E2-E5

In Column E (E2:E5), merge address columns using TEXTJOIN with delimiter ", " and ignore_empty set to TRUE.

Objective #2

Cell: B8

In cell B8, count the total assembled mailing addresses using COUNTA.

Objective #3

Cell: C8

In cell C8, count addresses that include a suite or unit number using COUNTA(B2:B5).

Objective #4

Cell: D8

In cell D8, count standalone home addresses with blank suites using COUNTBLANK(B2:B5).

Solution & Formula Breakdown

Let's examine how the modern TEXTJOIN function eliminates extra delimiters and handles empty cells:

1. Merging Columns with TEXTJOIN (E2:E5)

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

Let's break down the 3 arguments inside TEXTJOIN:

Delimiter (", "): The text string placed between every non-empty cell.
Ignore Empty (TRUE): Tells Excel to seamlessly skip blank cells (like empty Suite in Row 3) without inserting extra commas.
Text Range (A2:D2): The horizontal range containing the address pieces.
Row 2 (With Suite): Joins "123 Main St", "Suite 400", "Seattle", "WA" → "123 Main St, Suite 400, Seattle, WA"
Row 3 (No Suite): Skips empty B3 → "456 Oak Ave, Portland, OR" (clean, no double comma!)
Row 4 (With Apt): Joins "789 Pine Blvd", "Apt 2B", "San Francisco", "CA" → "789 Pine Blvd, Apt 2B, San Francisco, CA"
Row 5 (No Suite): Skips empty B5 → "321 Elm Rd, Austin, TX"

2. Audit Summary Formulas (B8, C8, D8)

Cell B8 (Total Labels): =COUNTA(E2:E5) counts all generated mailing labels → 4.
Cell C8 (Units / Suites): =COUNTA(B2:B5) counts non-empty suite entries → 2.
Cell D8 (Standalone Homes): =COUNTBLANK(B2:B5) counts empty suite cells → 2.

Before TEXTJOIN, combining text required messy nested IF formulas to avoid double commas. TEXTJOIN with TRUE makes column merging simple, clean, and robust!