A while back, I wrote a blog about using Excel to manipulate text. The functions in that blog allow you to rip text apart and put the pieces together so you can parse out pieces of your text strings. Since that writing, Microsoft has introduced two additional functions that can help you search for specific text values. These functions are TEXTBEFORE and TEXTAFTER.

• TEXTBEFORE will return text before the characters you are searching for
• TEXTAFTER will return text after the characters you are searching for

Both functions have two required parameters:

=TEXTBEFORE(text,delimiter)

=TEXTAFTER(text,delimiter)

• The text parameter defines the string value you are searching for
• The delimiter parameter defines the point after you want to extract

In addition, there are optional parameters that can be used to determine case sensitivity and what to do if no match if found.

The following example shows how to use these functions:

This approach will simplify your searches by eliminating the need to merge functions such as FIND, and LEN and LEFT/RIGHT together.

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: How to Set Up Action Buttons in Planning Analytics for Excel

IBM Planning Analytics Tips & Tricks: Excel SEQUENCE

IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process