Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Mar 10, 2020
Solved

why do textjoin with if return a zero

Hi,   if I use textjoin with If to join mutlipe rows, it will return a 0 if the cell is empty   Example Left: (with zero) =TEXTJOIN(" - ";TRUE;(IF(Tabell3[Number]=101;Tabell3[Text];""))) ...
excel
  • ExcelExciting's avatar
    ExcelExciting
    Mar 10, 2020

    Hi Hogstad_Raadgivning 

     

    There are many ways to write the formula, my way of constructing of the formula read as below

    =TEXTJOIN("-",TRUE,IF((NOT(ISBLANK(Table1[Text]))*(Table1[Number]=101))=1,Table1[Text],""))

     

    Reason of returning Zero

    When you are doing the array calculation of comparing 101 with Number column it is check all the rows which has 101 & return the result as TRUE,

     

    we need to construct our formula in such a way by ignoring the blank values from our array

     

    So I have to compare the Text Column [Non Blank] with Number column [101] 

     

    =(NOT(ISBLANK(Table1[Text]))*(Table1[Number]=101))=1

     

    and select only the non empty row and pass it as logical test to IF Function & joint the text with TEXTJOIN which ignore the blank value.

     

    Attached is the file for your ready reference

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer.

     

ExcelExciting's avatar
ExcelExciting
MVP
Mar 10, 2020

Hi Hogstad_Raadgivning 

 

There are many ways to write the formula, my way of constructing of the formula read as below

=TEXTJOIN("-",TRUE,IF((NOT(ISBLANK(Table1[Text]))*(Table1[Number]=101))=1,Table1[Text],""))

 

Reason of returning Zero

When you are doing the array calculation of comparing 101 with Number column it is check all the rows which has 101 & return the result as TRUE,

 

we need to construct our formula in such a way by ignoring the blank values from our array

 

So I have to compare the Text Column [Non Blank] with Number column [101] 

 

=(NOT(ISBLANK(Table1[Text]))*(Table1[Number]=101))=1

 

and select only the non empty row and pass it as logical test to IF Function & joint the text with TEXTJOIN which ignore the blank value.

 

Attached is the file for your ready reference

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

 

Resources