Have You Got What it Text? Introducing ARRAYTOTEXT and VALUETOTEXT

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:

Introducing ARRAYTOTEXT

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.

And There’s More: VALUETOTEXT

ARRAYTOTEXT is not the only new, “unintroduced” function.  Ladies and gentlemen, may I also present to you VALUETOTEXT.  This may be seen as a “building block” because it allows you to access the individual text fields returned in ARRAYTOTEXT and then use them in other ways.

VALUETOTEXT also has two – hopefully familiar – arguments:

The arguments are as follows:

• value is a required argument and represents a single value you wish to convert to text.  This is why this function is considered a “building block” for ARRAYTOTEXT 
  
• format is an optional argument.  This has two options:

In its most basic form, VALUETOTEXT converts a value to text:

From this illustration, you may think TEXT(,”General”) and VALUETOTEXT are interchangeable.  However, they differ in several ways:

• TEXT may be used to change a value to numerous different formats, e.g. “mmm yy” or “$#,##0”.  This is not the case with VALUETOTEXT 
  
• TEXT(,”General”) will currently produce an #VALUE! error when used with the Stocks and Geography Data Types.  VALUETOTEXT has no such issues
• VALUETOTEXT supports additional encodings and may be expanded in the future (i.e. it is “future-proofed”)
• VALUETOTEXT is locale friendly.

As a final, more contrived here is how VALUETOTEXT might be used as a building block.  Imagine you wanted to put AND rather than a comma between items in a list.  You could use

English teachers with a nervous disposition should look away at this point.

>Dynamic arrays allow you to enter this formula without the need for CTRL + SHIFT + ENTER. 

Word to the Wise

These two new functions may not appear to represent the most exciting Excel announcement of recent times, but I cannot help thinking these are necessary stepping stones to something greater. 

With no announcement supporting these functions (yet!), it’s not clear as to the availability of these functions.  Our best guess is they are probably being flighted only in Microsoft 365 Beta Channel, but perhaps only to a percentage of Excel Insiders (i.e. not everyone in this channel will have it).

I’d keep watching this space for more details…