TEXTJOIN() on an array incorrectly returns a blank

3

1

TL;DR: TEXTJOIN() on an array returns a blank - can't use VBA to solve the issue in an alternative way.

I've created several sheets with heavy dependence on Array functions due to limitations disallowing me from creating a macro-enabled workbook.

So far, this is working out quite alright, but I am running into a problem with TEXTJOIN().

Basically, it's the following formula:

{=TEXTJOIN(CHAR(10);TRUE;IF(A3:A100="test";B3:B100;""))}

returning a blank (not an error). Using the 'Evaluate Formula' tool shows that the penultimate step generates the right array (with two non null string values in it). However, the next step is empty. See the screenshot:

Evaluate Formula for the Textjoin function

Edit: clearer picture:

Evaluate Formula for Textjoin, cell remains empty

This been puzzling me for quite some time now, so any help or alternative solutions that do not rely on VBA would be much appreciated!

hooge

Posted 2018-06-19T13:47:49.327

Reputation: 31

1That is very strange. (I don't have Excel 2016 so I'm using a poly-fill UDF which works fine with your formula.) What happens if you change the CHAR(10) to something else like "#"? – robinCTS – 2018-06-19T14:13:35.037

1the only error i can see is the misplaced { it should be {=TEXTJOIN(CHAR(10);TRUE;IF(A3:A100="test";B3:B100;""))} Your formula works fine for me in 2016 – PeterH – 2018-06-19T14:22:41.860

I tried with different delimiter, the problem is still the same. @PeterH: Can you help me see what I'm missing? What exactly is the difference between our formula's? – hooge – 2018-06-19T14:49:35.423

@hooge I am at a loss, I copied your EXACT formula and it works – PeterH – 2018-06-19T14:51:39.547

1Could you have formatted the text to white? – Scott Craner – 2018-06-19T15:01:31.713

@ScottCraner: considered that and already tried changing the font to no avail. Also, the last step of the Evaluate Formula is also empty, it really does seem empty. – hooge – 2018-06-19T15:07:59.233

@ScottCraner Good one! Unfortunately, hooge has said that the next Evaluate Formula step results in a blank. – robinCTS – 2018-06-19T15:08:07.397

1Try restarting Excel, creating a new blank workbook, and then copy-pasting the formula from the question. (And add the data, of course.) – robinCTS – 2018-06-19T15:10:25.690

1I wonder if there's a limit on the length of the array? You've said that you've tried one cell but have you tried something else less than 500 cells? Do two cells work? – robinCTS – 2018-06-19T15:23:45.223

Answers

0

I suspect your formula is working properly but do not have Excel 2016 to verify my suspicions. What I think is happening is you are concatenating 100 carriage returns. This will result in output that will be somewhere in excess of 1000 pixels high but Excel will only permit a row height of 407 pixels so the visible text portion of the output is being hidden from view.

You can confirm my suspicions with the following test: copy the output cell (Summary!C21) and paste it into a blank Notepad session. If the cell contains data, you will be able to see it in Notepad. Alternatively, you might convert the formula to its value using [F2] then [F9] and then trying to scroll up and down the output.

If my suspicion is correct, I am afraid you will need to research another method of displaying the data you require - probably by using additional formulas to filter out the data you wish to display.

Ursus

Posted 2018-06-19T13:47:49.327

Reputation: 1

Asked: 2018-06-19T13:47:49.327

Viewed: 726 times

Active: 2018-10-10T12:59:11.137