IBM Planning Analytics Tips and Tricks: Excel’s ARRAYTOTEXT Function

April 2, 2024

Have you ever needed to export Excel data in a format that can be read by other systems? Although csv files are widely accepted, many legacy systems expect data in an array format. This can be done using the ARRAYTOTEXT formula in Excel.

The syntax of the ARRAYTOTEXT formula is:

=ARRAYTOTEXT(array, format)

Array: the text to convert to an array
Format: the format to apply on conversion
- The only valid values for this parameter are 0 and 1

A standard array format uses a set structure:

The array enclosed by curly braces.
All items in the same row are separated by commas
The end of a row is indicated by a semicolon

For example, this data set …

A screenshot of a computer

Description automatically generated

… would appear like this in array notation:

{“Row1Col1″,”Row1Col2″;”Row2Col1″,”Row2Col2”}

However, the function does not force you to use the standard array format. Using the following table as an example:

Format = 0 (also called the concise format) will join all the data into a single comma delimited list, similar to the TEXTJOIN formula.

Format 1 (also called strict format) will convert the data into an array format. Every item on the same row is separated by a comma and the end of a row is indicated by a semicolon.

As an added feature, the ARRAYTOTEXT formula detects data types and does not convert the numbers to text (numbers are not enclosed by quotation marks).

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!

Author

Ivan Cepero

View all posts