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];"")))

 

Example right

=TEXJOIN(" - ";TRUE;Tabell1[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.

     

3 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Mar 10, 2020

    Hogstad_Raadgivning 

    The IF() causes the problem.

    Workaround:

    Add another column Text2:

    =IF([@Text]="","",[@Text])

    And then: 

    =TEXTJOIN(" - ";TRUE;(IF(Tabell3[Number]=101;Tabell3[Text2];"")))

     

  • 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