Join building, floor and room codes

Turn building A, floor 02 and room 004B into BLDG A FL 02 RM 004B.

What you'll make

In Excel: A + 02 + 004B becomes BLDG A FL 02 RM 004B.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1IF(A8="",…,"BLDG "&A8)Writes BLDG and the building, if there is one."BLDG A"
2IF(B8="",…,"FL "&B8)Writes FL and the floor, if there is one."FL 02"
3IF(C8="",…,"RM "&C8)Writes RM and the room, if there is one."RM 004B"
4TEXTJOIN( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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