Microsoft Excel has recently introduced a new version of the CONCATENATE function called CONCAT. The purpose is similar in that the new function still combines text from multiple ranges and/or strings. CONCAT will eventually replace CONCATENATE, but Microsoft has not released plans to sunset the original function to ensure compatibility with older versions of Excel.
The CONCAT function expects at least one text parameter value, with a maximum of 253 arguments. For example:
| =CONCAT(A1,B1,C1,D1,A2,B2,C2,D2) |
returns “Tobeornottobe.” The new function also allows you to combine text ranges. For example:
| =CONCAT(A1:D2) |
returns a string consisting of the values in cells A1, B1, C1, D1, A2, B2, C2, and D2. In this case, “Tobeornottobe” will once again be returned.
Similarly, the TEXTJOIN function joins text from multiple ranges and/or strings but includes two additional parameters. These parameters allow you to specify the delimiter and determine if empty cells are ignored. For example:
| =TEXTJOIN(“ ”, TRUE, A1:D2) |
returns “To be or not to be,” whereas
| =TEXTJOIN(“ ”, FALSE, A1:D2) |
returns “To be or not to be.” In the last example, the spaces have been doubled wherever there are empty cells because the second parameter has been set to FALSE.
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!
Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!
Read related posts with Excel Tips & Tricks and using Excel with Planning Analytics:
IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP
IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula
IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets
