Category Archives: TEXTJOIN

#Excel Super Links #135 – shared by David Hager


Power BI – Using Change tables to compare data

Extracting images and themes from Power BI Desktop files

Calculating the Number of Unique Items in a Delimited String

Excel Games

Creating A Unique Delimited String From a Delimited String – Excel Formula Method With TEXTJOIN


#Excel Super Links #133 – shared by David Hager


Extract duplicate values with exceptions

Top Excel Formulas & Function Examples To Get Better At Microsoft Excel

Tom’s Tutorials For Excel: Returning a Formula’s Arguments

Generating a series of numbers in DAX

#Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges


#Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges


What if I told you that I had 2 non-contiguous 3D ranges in an Excel workbook and I wanted to return a single 1D array from those ranges. Impossible, right? No, it turns out that it is “easy”.

Prior to the introduction of the TEXTJOIN function, this would likely have been impossible. But, this function accepts native 3D ranges as range arguments. See:

It would have been nice if the technique was only a VBA solution, but although Textjoin is a VBA worksheet function in Excel, VBA will not accept a native 3D range as an argument. Likewise, a pure Excel formula solution would have been nice, and a method dows exist to do this

but it has severe limitations which prevents its use with a relatively large number of cells (maybe 150). The total number of characters that a cell can contain is 32,767 characters. This solution assumes that an average of 6 characters per cell plus a comma for each gives an approximate number of 4500 cells allowed.

Here is the solution.


It consists of the TEXTJOIN worksheet function with 2 non-contiguous 3D ranges arguments


And a simple VBA function which converts a comma delimited string into a 1D array.

Function ArrayFromCDS(MyString As String)

ArrayFromCDS = Split(MyString, “,”)

End Function

In the example file, the array produced contains all of the elements of the two 3D ranges (shown below).


The figure shows this formula in cell I2.


I hope that you will find this useful.

The example file can be downloaded here.


#Excel Super Links #131 – shared by David Hager


Excel Formulas Based Sudoku Solver

Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Converting a Formula to Values

Changing the culture of a Power BI Desktop file

Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager

#Excel Super Links #130 – shared by David Hager


Tom’s Tutorials For Excel: Counting All Data Types in a Range

What is Solver in Excel?

Working With Range.FormulaArray In VBA

15 Quick & Powerful ways to analyze Business Data

Extracting Ingredients from an #Excel List by Cost Category by David Hager

Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager


Documenting Excel formulas is an important step in the auditing of spreadsheets. Presented here is a new technique to aid in that process.

The utility of the recently added FORMULATEXT, ISFORMULA and TEXTJOIN functions is sometimes underestimated. The FORMULATEXT and ISFORMULA functions were introduced with Excel 2013 and the TEXTJOIN function is available only the Excel 2016 version which comes with an Office 365 subscription. In the technique demonstrated in this article, al three of these functions are used in a single formula. For the result, see the following figure:


The key formula (in cell K1) is


To understand how this formula works, lets break it down into its main parts.

ADDRESS(ROW(fRange),COLUMN(fRange)) returns an array of cell addresses for the desired range (in this case, fRange, which is E1:F13).

FORMULATEXT(fRange) returns an array of formulas for that range.

When these two formulas are concatenated with a colon, you get an array that looks like this.


In this example, formulas only exist in cells E13 and F13, with the rest of the array elements as #N/A. When the ISFORMULA function is applied to the range is question, the result is


So, the final array created by the IF function looks like this.


When this array is processed by the TEXTJOIN function, it affords a string of formulas with their corresponding cell locations.

Note that this technique can also be used with non-contiguous ranges. Since the TEXTJOIN function can use additional arrays as arguments (see ), the same array formula used in the 3rd argument of this TEXTJOIN example can be used in subsequent arguments, provided that the desired ranges for those arrays are different.

You can download the example file here.



#Excel Super Links #129 – shared by David Hager


Excel Magic Trick 1455: Convert Text Month Criteria to Upper & Lower Dates for SUMIFS Function

Excel Find and Remove Duplicates

Find last value in a column

UnicodeBox ()

Extracting Ingredients from an #Excel List by Cost Category by David Hager