Have you ever needed to parse a text string into different columns in Excel? This is easy to do via the text-to-columns wizard, but what if you wanted to do this via a formula? The TEXTSPLIT function allows you to split a text value across columns. It even lets you split values across rows!
The function has two required parameters:
=TEXTSPLIT(text,col_delimiter)
- The text parameter defines the string value you are searching for
- The column delimiter parameter defines the character(s) used for each split
In addition, there are optional parameters that can be used to determine case sensitivity, determine a row delimiter, and define what to do if two matches in a row are found
The following example shows how to use the function by searching for a space between each word and then putting each word into a new column. The source value is in cell A1 and a single TEXTSPLIT formula has been created in cell A2 (note: the text of the formula has been replicated onto cell A6 to show it in this image).
This next example shows a similar concept but uses a different parameter to instead put each word into a new row.
This approach will simplify your searches by eliminating the need to create multiple functions to parse the same set of data.
Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER
IBM Planning Analytics Tips & Tricks: Excel SEQUENCE
IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process
