Conditional TEXTJOIN

[chris01395]

I am trying to create a hyperlink to email several email addresses using the textjoin function, but I get the #VALUE error if the number of occurrences is exceeding a certain limit

Please see attached, the formula for Department B (with 13 instances) works fine, but for Department A (with 15 instances) does not work.

This is the formula I am using

=HYPERLINK("mailto:"&TEXTJOIN("; ",TRUE,IF(Table1[Department]=D3,Table1[Colleague Email],"")),"Email "&D3)

It is entered as an array formula (Ctrl + Shift + Enter)

If TextJoin is not suitable for this (potentially 50 occurrences), then would appreciate suggestions of alternative method.

[ben8563]

Please Login or Register  to view this content.

[XLent]

the issue is not TEXTJOIN but the 255 char limit on the HYPERLINK address itself, e.g.

=HYPERLINK("mailto:"&REPT("z",248)) -> mailto:zzzzz...
=HYPERLINK("mailto:"&REPT("z",249)) -> #VALUE!

so, following above, if you swap your delimiter from ;space to ; (thus saving space) your first calc will work, but how scalable this is in real life is debatable, of course.

FWIW, whilst it doesn't fix your issue, if you're using TEXTJOIN you can remove the Array by using FILTER (and UNIQUE to remove dupes should they exist)

=HYPERLINK("mailto:"&TEXTJOIN(";",,UNIQUE(FILTER(Table1[Colleague Email],Table1[Department]=D2))),"Email "&D2)

[chris01395]

@ben8563 Thank you for the proposal, as far as I can see the only change you make is to remove space from the delimiter? Whilst this does allow me to get one more occurrence in (I guess indicating the issue is with the length of the string), I still get the error when the occurrences continue to grow.

[chris01395]

Thanks @XLent for the pointers, now I know the issue I can explore possible solutions.

[ben8563]

Please Login or Register  to view this content.

have a try

[chris01395]

@ben8563, thank you for the suggestion, I could not get it work unfortunately, I think I need to update my Excel (did not recognise PHONETIC function).

In the meantime I come up with a work around to solve my original problem to a satisfactory level, basically if the string is less than or equal to 255 characters the Hyperlink works as intended, if it is greater than 255 then I just prompt the user to copy email addresses from another cell and paste directly into an email. For me this is fine.

I know I could have solved this with VBA, but would rather avoid that as my file is likely to end up on SharePoint, and that does not support macros.

Below is my solution and I reattach workbook with it for anyone that is interested;

Please Login or Register  to view this content.