Join building, floor and room codes
Turn building A, floor 02 and room 004B into BLDG A FL 02 RM 004B.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | IF(A8="",…,"BLDG "&A8) | Writes BLDG and the building, if there is one. | "BLDG A" |
| 2 | IF(B8="",…,"FL "&B8) | Writes FL and the floor, if there is one. | "FL 02" |
| 3 | IF(C8="",…,"RM "&C8) | Writes RM and the room, if there is one. | "RM 004B" |
| 4 | TEXTJOIN( … ) | Joins them with spaces and skips empty ones. | "BLDG A FL 02 RM 004B" |
The " " and TRUE inside TEXTJOIN mean: a space between, skip empty parts.
=TEXTJOIN(" ",TRUE,IF(A8="","","BLDG "&A8),IF(B8="","","FL "&B8),IF(C8="","","RM "&C8))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste building, floor and room codes into the green columns of the Template tab. Leave a level empty when there isn't one.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Paste each code into its own column. The formula never guesses a floor from a room number.
Microsoft's help: TEXTJOIN function
OTHER ROUTES YOU MIGHT LIKE

Remove a duplicated unit designator
APT + APT 5 → 5
Get the template →
Replace # unit marker with the unit type
12 Oak St #5 + APT → 12 Oak St APT 5
Get the template →
Put one space around a hash unit marker
12 Oak St #5 → 12 Oak St # 5
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →