match and textjoin formulas limits / behaviour

Question

Hi,

I'm running Excel 365 version 1906, 11727.20230 from monthly channel (targeted)

TEXTJOIN

when I use TEXTJOIN in array formula like this

{=";"&TEXTJOIN(";",**TRUE,**IF(IFERROR(SEARCH("QWE",$M$1:$M$100)>0,FALSE),ROW($M$1:$M$100),""))&";"}

despite setting IgnoreEmpty param to TRUE, formula quite often forgets to strip empty fields; what's funny, when I re-run it manually in those places, after 2-3 times finally it works fine. Does anyone know what can cause such behaviour?

MATCH

When you run:

=MATCH("*;QWE;*";A:A;0)

to perform check on cell longer than 256 characters, it'll fail and return #N/A

there's a workaround to this problem:

=MATCH(TRUE;FIND("*;QWE*;";A:A)>0;0)

but it takes ages in my file to complete (aprox. 5000 rows)

I cannot find any info that there's any limitation, so I assume this is a bug... or this is well known?

Answer 1

I tried to replicate the issue with my home PC running Excel 365 (ver:1906 11727.20230 monthly channel) and.... cannot.

anyway here's the link

go to dict_bundles sheet, it's saved with results (wrong ones) with my other pc having the issue.

Answer 2

Hi Marcin,

Thank you for sharing the updates with us. Since the formula has the same behavior on far less data on your side, I'd appreciate it if you may share a copy of your test workbook (without any personal data), so that we could check the pattern of the data and further investigate this behavior. We could also test the workbook on different versions of Excel applications and see if the change between versions are involved in this behavior. To protect your privacy, you may share the file in Personal OneDrive.

Best Regards,

Nathan

Answer 3

Hi Nathan,

255 chars limit is simple - I didn't know about it; voted on UserVoice, but case is open sine 2016 and now it have 8 votes... I think that it might be wrong place. I believe

for TEXTJOIN I tested it further in attempt to find a pattern and:

enviroment: I'm testing 1037 rows (148 of then has "*") against 4672 rows of data; I found that my other file, way simplier (200 rows against 50) have same issue - I conclude this is not dataset size related issue

also that, if failure happens, always same rows are failing lead me to the conclusion this is something about data itself that is being processed

both type of searched texts are failing with or without asterix chars

it never fails when pasted: double click on bottom-right corner to perform top-down paste; standard copy-paste, special copy-paste fomulas

it randomly fails then re-entered: F2 followed by Ctrl-Shift-Enter; cleared, entered, Ctrl-Shift-Enter

ultimate test: I made 1:1 - one searched test against one in array, but array size still 4672 rows

searched text: ""

searched in:

=";"&TEXTJOIN(";",TRUE,IF(IFERROR(SEARCH(A1,temp!$A$1:$A$4672)>0,FALSE),ROW(temp!$A$1:$A$4672),""))&";"

in temp!A1: "" all the rest is empty

result atfer Ctrl-Shift-Enter:

";1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;" --> there, more, stripped, but there're all almost 4700

Shift-F9 -> due to obvious reasons I'm working in manual calc mode, result:

first run - no change

second run, result: ";1;"

when I run this:

=";"&TEXTJOIN(";",TRUE,IF(IFERROR(SEARCH(A1,temp!$A$1:$A$10)>0,FALSE),ROW(temp!$A$1:$A$10),""))&";"

so agains only 10 row array it seems to never fail

I've not found any pattern, but this what I see looks like this is something related to calculation chain, at which point formula may or may not properly assess whether cells are empty or not.

EDIT:: pics are missing, replaced with texts

Answer 4

Hi Marcin,

May I know if you have checked my reply above in regard of your TEXTJOIN and MATCH formulae? Please let us know if you have any further concerns.

Best Regards,

Nathan

Answer 5

Hi Marcin,

Based on my test with your TEXTJOIN formula, all the empty cells are striped in the result. Considering that you mentioned that it works fine after rerunning it 2 to 3 times, and that your cells contain long texts and large amount of data items, this behavior may be because the formula needs more time to handle the data. You may try creating a new formula with smaller range and shorter texts in each cells to see if the issue would appear.

In regard of your concern about the 255 characters limitation of the MATCH() function, I would suggest voting for the following thread in the Excel for Windows forum in UserVoice so that the development team would find a solution to lift the limitation in the future:

Remove the 255 character limit from Conditional Formatting, Match() and CountIf() functions

Please let us know if you have any further concerns.

Best Regards,

Nathan