Have you ever wanted to manipulate text within Excel? Excel has a series of functions that allow you to parse text strings based on whether you want to reference the left, the right, or the middle of the cell contents.
For example, assume cell A2 contains the text “Planning Analytics”. The functions LEFT and RIGHT will allow you to view characters at the start of the string or at the end of the string.
=LEFT(A2,3) will give a result of “Pla”
=RIGHT(A2,3) will give a result of “ics”
The function MID will allow you to look within the string by defining both a starting point and the number of characters in the result.
=MID(A2,3,3) will give a result of “ann”
But what if you didn’t know the number of characters? You can use functions to find a specific set of characters and to determine the overall length of the string.
=FIND(“A”,A2)-1 will give a result of “9”, which signifies that the string begins at the 9th character
=LEN(A2) will give a result of “18”, which signifies that the string contains 18 total characters
You can then put these pieces together to find a specific set of data, such as the second word in the string. Assuming that D6 contains the LEN function and D5 contains the FIND function:
=RIGHT(A2,D6-D5) will give a result of “Analytics”
These Excel functions will allow you to quickly parse out pieces of a text string which can then be used in your spreadsheets.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more Excel tips in our IBM Planning Analytics Tips & Tricks series:
IBM Planning Analytics Tips & Tricks: Excel’s NETWORKDAYS Function
IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation
