Forum Discussion
How to get textjoin to output blank instead of 0, 0, 0, .....?
In my current job, when we're on site, we have to write down the roll numbers of the material used and which panel number it was used in along with other info.
We then have to do a summary sheet for the report which includes all of the roll numbers used and which panels they were used in. This used to be done manually, but I have managed to do it with TEXTJOIN. It works great, but when there is no roll number to match to, it gives me 0, 0, 0, 0, ........
Is there any way to stop this from happening?
The formula that I am using is =TEXTJOIN(", ",TRUE,IF('GCL PD Cell'!$B$5:$B$356='GCL Roll Inv'!$A3,'GCL PD Cell'!$A$5:$A$356,""))
2 Replies
- I managed to work it out by adding an "AND" statement to the conditions of the IF statement.
Final formula looks like this:
=TEXTJOIN(", ",TRUE,IF(('GCL PD Cell'!$B$5:$B$2000='GCL Roll Inv'!$A3)*('GCL Roll Inv'!$A3<>""),'GCL PD Cell'!$A$5:$A$2000,""))
With * taking the place of the AND function.=TEXTJOIN(", ";TRUE;IF('GCL PD Cell'!$B$5:$B$356='GCL Roll Inv'!$A3;IF('GCL PD Cell'!$A$5:$A$356=0;"";'GCL PD Cell'!$A$5:$A$356);""))An alternative could be a nested IF statement.
