The TextSplit function

The TextSplit function splits a string using a separator character and returns one of the segments.

A string can hold more than one value if you separate the different values, or segments, using a separator character. WrapCreator supports delimited lists for some operations, e.g. to provide options for the Dynamic Dropdown widget. When working with this kind of aggregated string values, the TextSplit function allows you to parse the string and use each segment separately.

Example: You have access to all the three-letter ISO-3166-1 alpha-3 country codes in a delimited list.

ABW|AFG|AGO|AIA|ALA|ALB|AND|ARE|ARG|ARM|...

If you have this list stored in cell C2, and you want to know the country code in the third segment, you can use the TextSplit function like this:

=TextSplit(C2, "|", 3)

The above function will return “AGO”, the ISO-3166-1 alpha-3 code for Angola.

Testing in Excel

Excel does not support the TextSplit function natively. The definition and implementation of this function are provided by the WrapCreator add-in, also in Excel. If you use the TextSplit function in your spreadsheets, it will function and give results already in Excel, as long as its three parameters can be evaluated. If your function call takes the input string from a cell that is empty by default, you will receive a #VALUE! error message in Excel but the function will operate correctly when converted to a Wrap, as long as the Wrap instance has values for all the three parameters.

Function reference

Example

=TextSplit("First,Second",",",2)

In the example above, the comma-separated input string will be automatically split into two segments. Since the formula is requesting segment 2, “Second” is returned.

Format and parameters

=TextSplit(input_string, segment_separator, segment_to_return)

input_string

The input string is usually taken from a cell that you reference in the spreadsheet, e.g. B1. You can also enter the input string directly into the formula, surrounded by double quotes, e.g. “Response;Yes;No”, using semicolons to separate the segments.

An empty input_string is not allowed, and gives a #VALUE! error. A blank input string returns an error message in the result. A simple way to avoid both these situations is to always append the designated segment separator at the end of the trimmed input_string, e.g.

=TextSplit(Trim(B1) & ">", ">", 2)

segment_separator

A new segment is started for every occurrence of the separator in the input string. The separator can consist of one or more characters, so if you want to use a separator like “a kiss is still a kiss” you are free to do so.

You can use blanks inside the separator string, but you cannot have just a blank or space character as the segment separator. If your input string is separated using blanks, you can use Excel’s Substitute function to replace the blanks with the separator character.

=TextSplit(Substitute(B1," ","*"), "*", 18)

segment_to_return

Designates which of the segments from the input string to return. If the input string contains “..   Third”, specifying “3” with “.” as segment separator will return the third segment of the input string, i.e. ”   Third”.

If you specify a segment number that is higher than the actual number of segments, the function will return an error message in the result. If your implementation would work fine if segment numbers beyond the end of the input string always return the last segment, you can use a function call like this:

=TextSplit(B1, B2, Min(B3, FIND(B2, B1) + 1))

An empty segment_to_return is not allowed, and gives a #VALUE! error. A value of 0 returns an error message in the result. To avoid these situations, format your function call like this to always return the first segment if the segment number is empty, blank or zero:

=TextSplit(B1, B2, Max(0 & Trim(B3), 1))

A negative segment number returns an error message in the result. You may want to use negative numbers to instead count segments backward, from the end of the input_string. To provide this functionality, use a function call similar to this:

=TextSplit(B1, B2, IF(B3 < 1, FIND(B2, B1) + 2 + B3, B3))

Removing leading and trailing blanks

The returned segment is not trimmed for leading or trailing blanks. To remove leading and trailing blanks from the returned segment, use the Trim function:

=Trim(TextSplit("  A>  B > C  ", ">", 3))

The function call above would return just “C” without the leading and trailing blanks in the input string.