Multi-Line Room Name Formula Revisited

I had time to put together a DO WHILE LOOP to strip out any instances of back-to-back spaces that could result from either a blank second line or spaces typed by the user when combining three separate room name lines into one string for use in a schedule. I posted a sample file with the new formula to the Autodesk Architectural Desktop 2005 Discussion Group. For this example, I chose not to use the array method of combining the strings, discussed in a previous blog article, but simply concatenated all three single-line properties with separating spaces. I did use the TRIM function to remove any leading or trailing spaces, then applied the DO loop to replace any back-to-back spaces with a single space.

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
and
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, "  ", " ")
Loop
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.