CONCATENATE and JOIN in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a list of full product names by combining brand and product type, and a summary string of all product categories separated by commas.

📊 Data: You have a table with columns: Brand, Product Type, and Category for each product.

🎯 Deliverable: Create a new column with full product names by joining Brand and Product Type, and create a single cell that lists all unique categories separated by commas.

Sample Data

Brand	Product Type	Category
Acme	Toothbrush	Personal Care
BrightCo	Shampoo	Personal Care
FreshFarm	Apple	Food
Acme	Soap	Personal Care
BrightCo	Juice	Beverages
FreshFarm	Carrot	Food
Acme	Water Bottle	Beverages
BrightCo	Toothpaste	Personal Care

1

Step 1: Create a new column named 'Full Product Name' next to 'Product Type'.

In the first row of the new column (e.g., D2), enter the formula: =CONCATENATE(A2, " ", B2)

Expected Result

For row 2, the cell shows: "Acme Toothbrush"

2

Step 2: Copy the formula down the 'Full Product Name' column for all product rows.

Drag the fill handle from D2 down to D9 to apply =CONCATENATE(A3, " ", B3), etc.

Expected Result

Each row in the new column shows combined Brand and Product Type, e.g., "BrightCo Shampoo", "FreshFarm Apple"

3

Step 3: Create a cell to list all unique product categories separated by commas.

In a separate cell (e.g., F2), enter: =JOIN(", ", UNIQUE(C2:C9))

Expected Result

The cell shows: "Personal Care, Food, Beverages"

Final Result

Full Product Name Column:
Acme Toothbrush
BrightCo Shampoo
FreshFarm Apple
Acme Soap
BrightCo Juice
FreshFarm Carrot
Acme Water Bottle
BrightCo Toothpaste

Categories Summary Cell:
Personal Care, Food, Beverages

✓Full product names are created by joining Brand and Product Type with a space.

✓All unique product categories are combined into one comma-separated list.

Bonus Challenge

Modify the full product name to include the category in parentheses, e.g., "Acme Toothbrush (Personal Care)".

Show Hint

Use CONCATENATE or the & operator to join Brand, Product Type, and Category with spaces and parentheses.