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.
After we introduced this function, Excel got a new function with the same name. To avoid name conflicts, the function call for the ExcelWraps function was changed to TextSplit_EW.
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_EW(C2, "|", 3)
The above function will return “AGO”, the ISO-3166-1 alpha-3 code for Angola.
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, if its three parameters can be evaluated. If your function call fetches one or more parameters from cells in the spreadsheet that are empty or blank, the function just returns nothing – an empty string.
The function always works correctly when converted to a Wrap, as long as the Wrap instance provides correct values for all three parameters.
The opposite of TextSplit is TextGlue. If you use Excel 2019 or later, it provides the TextJoin function which works exactly like TextGlue.
=TextSplit_EW("First,Second",",",2)
In the example above, a comma-separated input string will be automatically split into two segments. Since the formula is requesting segment 2, “Second” is returned.
=TextSplit_EW(input_string, segment_separator, segment_to_return)
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.
If the input_string is blank or empty, the TextSplit function returns an empty string.
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.
If the segment_separator is blank or empty, the TextSplit function returns an empty string.
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 a non-blank separator character.
=TextSplit_EW(Substitute(B1," ","*"), "*", 18)
Designates which of the segments from the input string to return. If the input string contains “.. Third”, specifying “3” with “.” as the 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 TextSplit function returns an empty string. 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_EW(B1, B2, Min(B3, FIND(B2, B1) + 1))
If the segment_to_return is zero, blank, or empty, the TextSplit function returns an empty string. 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_EW(B1, B2, Max(0 & Trim(B3), 1))
If the segment number is negative, the TextSplit function always returns an empty string. 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_EW(B1, B2, IF(B3 < 1, FIND(B2, B1) + 2 + B3, B3))
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_EW(" A> B > C ", ">", 3))
The function call above would return just “C” without the leading and trailing blanks in the input string.
You can nest multiple TextSplit functions to retrieve multidimensional data, e.g. rows and columns from a MyWraps2() function call. If the MyWraps2 function is in a cell called data_extract, you could use the following nested TextSplit function to extract the second field from the second extracted instace:
=TEXTSPLIT_EW(TEXTSPLIT_EW(data_extract, rowsep, instance_no), colsep, field_no)
Assuming you provide the following input values:
the function call above would return “row 2 col 2”.