List Accommodations
I need to combine all 'Accommodations' (e.g., Extended Time, Quiet Room, Scribe) for a student into a single, comma-separated list within one cell, ignoring any blank accommodation fields.
=TEXTJOIN(", ", TRUE, B2:D2) How it works: The TEXTJOIN function is ideal for this task because it allows you to combine text from multiple ranges or strings with a specified delimiter. The `TRUE` argument is crucial here as it tells Excel to ignore any empty cells within the selected range, preventing unnecessary delimiters (like ", ,") in the final list. This ensures a clean and concise list of only the provided accommodations.
Data Setup
| Student Name | Accommodation 1 | Accommodation 2 | Accommodation 3 |
|---|---|---|---|
| Alice | Extended Time | Quiet Room | Scribe |
| Bob | Extra Breaks | Visual Aids | |
| Charlie | Preferential Seating |
Step-by-Step Guide
Select the cell where you want the combined list to appear (e.g., E2).
Type `=TEXTJOIN("`
Enter the delimiter you want between accommodations, for example, `, ` (comma followed by a space).
Type `", TRUE, ` to indicate that empty cells should be ignored.
Select the range of cells containing the accommodations for the first student (e.g., `B2:D2`).
Close the parenthesis `)`.
Press Enter.
Drag the fill handle down to apply the formula to other students.