Array to String and Back Again

Just as it's common to convert an array of values into a single string for display, it is also common to split a string into multiple pieces. OOo Basic provides these abilities with the functions Join and Split.

The first argument to the Join function is a one-dimensional array. Any other dimension causes a run-time error. The elements in the array are concatenated with an optional delimiter string between each element. The default delimiter is a single space.

 Join(Array(1, 2, 3))      '1 2 3 using the default delimiter Join(Array(1, 2, 3), "X") '1X2X3 specifying a delimiter Join(Array(1, 2, 3), "")  '123   specifying an empty delimiter 

The Split function returns a Variant array of strings, created by breaking a string into multiple strings based on a delimiter. In other words, it parses a string into pieces with one command. The delimiter separates portions of the string. For example, the delimiter "XY" splits " 12XY11XY22" into the strings ("12", "11", "22"). The delimiter defaults to a space but can be any string expression with length greater than zero.

 Split("1 2 3")            'return Array("1" "2", "3") split on " " Split("1, 2, 3", , ")     'return Array("1", "2", "3") split on ", " 

The optional third argument is used to limit the size of the returned array. This is used only to limit the returned size and has no effect if the returned size is less than the limit. For example, the 4 in Split(" 1X2X3", "X", 4) has no effect because the returned array has only three elements. If the size is limited, however, the last element in the array contains the remainder of the unparsed string.

 Split("1, 2, 3", ", ", 2) 'return Array("1", "2, 3") split on ", " 

Warning  

The statement Split("0 1 2 3", 2) converts the 2 to a string and uses it as the delimiter. The returned array contains two elements, "0 1" and " 3". You must specify the delimiter if you want to specify the number of strings returned. The correct format is Split("0 1 2 3", "", 2).

The Split function assumes that a string comes before and after each delimiter, even if the string has length zero.

 Split("X1XX2X", "X") = ("", "1", "", "2", "") 

The first returned string is empty because the first argument contains a leading delimiter. Two consecutive delimiters produce an empty string between the "1" and the the "2". Finally, the trailing string is empty because there is a trailing delimiter.

The Split function is almost the inverse of the Join function. The Join function can use a zero-length string as the delimiter, but the Split function cannot. If the joined string contains the delimiter, splitting the string will produce a different set of strings. For example, joining "a b" and "c" with a space produces "a b c". Splitting this with a space produces ("a", "b", "c"), which is not the original set of strings.

Bug  

The OOo help incorrectly states that the delimiter can be only one character long; however, it can be any string. An empty string, however, causes the Split function to return an invalid empty array.

I spent a lot of time writing and debugging a macro to parse through a string to remove all occurrences of the text "Sbx". Some months later, I learned about the Split and Join functions. The code is significantly smaller and faster:

 Join(Split(s, "Sbx"), "")