A to Z of Excel Functions: The ARRAYTOTEXT Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the ARRAYTOTEXT function.

The ARRAYTOTEXT function

This one is out of order – but that’s because it’s just been released.  So back to the A’s we go!!  Imagine you had a list of countries, selected randomly:

If I wanted to put these into a “grammatical” list, I could simply use the TEXTJOIN function, viz.

is a very elegant way to combine all items into a punctuated list.  That’s great, but let’s complicate it.  Instead of doing this, imagine I had converted these country names to the Geography Data Type, by highlighting the cells and changing them:

This causes a problem for our previous formula:

To get the formula to work, we need to extract the Name field from the Geography Data Types:

In fact, as soon as you add the period (.) to the range reference, the world (quite literally!) is your oyster, e.g. if you just type the dot: 

You can see a whole new world opens up.  For example:

The concern is though, for some people, this may be a little too much and they may very well just want to convert what they have to a text list.  This is where the first of two new – and unannounced – Excel functions comes to the rescue: ARRAYTOTEXT:

Notice that you neither need to supply the name field explicitly nor the delimiter.  In its simplest form, it tries to keep things simple.  ARRAYTOTEXT follows your locale (regional setting) convention, so, here in Australia, we use a comma as a list delimiter, but this will not always be the case.

ARRAYTOTEXT has two arguments:

The arguments are as follows:

• array is a required argument and represents the array that needs to be listed.  This means the range may have more than one row and / or more than one column:

• Note that the array is listed like reading a book in English – left to right, then down the page.  This is different to how >INDEX works for example
• format is an optional argument.  This has two options:
  

Here is the same example using the ‘Strict’ format:

Here, each column is separated by a comma and each column by a semi-colon.  The text is also shown contained with quotation marks (double inverted commas).

Unlike the TEXTJOIN function, it cannot suppress blanks:

Having said that, TEXTJOIN has its own problems with blanks and Geography or Stock Data Types:

This generates the #FIELD! error, which denotes that you have tried to retrieve data from a non-existent Data Type field (please see >here for more details).

It’s not just blank ranges that can cause this.  With the May release of Power BI Desktop, you can now curate featured tables to be referenced in Excel (Insider or Beta build).  This can help you easily link your Excel sheets with authoritative enterprise data.

In Excel’s data types gallery, you will be able to find data from featured tables in Power BI datasets.  For example, you can feature data on Customers, Product and Suppliers:

Additionally, by using Power BI’s certified and promoted datasets capabilities, you can enable members of your organisation to find and use relevant / refreshable data to assist in decision-making.

For example, the ‘Let’s Stop N Shop’ cell (below) is linked to curated, organisational data from Power BI, so you can find the contact information for the owner of the store right within your spreadsheet, viz.

This way you may generate “Power BI Data Types”.  In the example below, Human is a Power BI Data Type that has no name field:

The problem with TEXTJOIN persists.  There are other problems too: the TEXTJOIN formula does not consider the end user’s locale preferences, for instance.