The formula looks like this:
4/24/2008 UPDATE: I have put the code in a "code box" to avoid having two critical double spaces from being stripped from the code. If you are going to cut and paste this code, rather than look at the sample file linked to above, make certain that there are two spaces between the double quotation marks in the Do While line, and two spaces bewteen the first set of double quotation marks in the Replace function and one space between the second set of double quotation marks. In other words:
Do While InStr(strRoomName, "[SPACE][SPACE]") > 0
strRoomName = Replace (strRoomName, "[SPACE][SPACE]", "[SPACE]")
where you will substitute a space character for [SPACE].
strRoomName = Trim("[NameLine1]" & " " &
"[NameLine2]" & " " & "[NameLine3]")
Do While InStr(strRoomName, " ") > 0
strRoomName = Replace (strRoomName, " ", " ")
RESULT = strRoomName
Note that the initial strRoomName assignment code is all on one line in my formula; I put it on two lines here to fit the blog format better.
The Trim command takes care of any leading or trailing spaces in the concatenated string. This could happen if NameLine1 or NameLine3 was a null string. The InStr function searches the string for instances of two back to back spaces; it returns an integer representing the position of the first one found, or 0 if none are found. If at least one "double space" is found, the loop is entered. The Replace function goes through the string and replaces all "double spaces" with single spaces. The loop ends here, and the string is tested once again for any "double spaces". There could still be some after the first pass; for example, a string with four spaces in a row would have the first two replaced with one space, then the third and fourth replaced with one space, after one pass of the Replace function, leaving two spaces in a row. By using a loop, the string is run through the Replace function until only single spaces remain. Finally, the processed string is passed as the RESULT of the formula.
Perhaps three-line room name tags are not something you use, but you may find the principles used here can be applied to other situations where you want to combine multiple string properties into one string and can not be certain that there will always be a non-null value in each property.