Forum Discussion
Why does this funcyion return zero wwhen the cell i empty.
Hi,
This is the situation. In this project we have 3 tablers.
1 for a weekplan (mon -> Sun) that table is a combination of
A Projectplan and a
A Otherplan (that includes Vacations, Training, Inspection....)
These tables has columns from Mon1 Tue1......... Fri52 Sat52 Where the number is the week number.
It works, but it return a 0 if the the specified row in Mon1 is empty.
Function
=TEXT.COMBINE(" - ";TRUE;IF(t_teamleder[AnsattID]=[@AnsattID];INDIRECT("t_teamleder[Mon"&$I$2&"]");"");HVIS(t_montasje[AnsattID]=[@AnsattID];INDIRECT("t_montasje[Mon"&$I$2&"]");""))
So I search in each table, on the wanted column (INDIRECT), and return text if there is an EmployeID Match (AnsattID), and combine the text in one cell.
The result:
If it find somtehing in a Cell, it return the wanted text. If the cell is Empy, a 0 is returned
Why the 0?
Best Regards
- Geir
4 Replies
The issue looks to be with TEXT.COMBINE - is that a user defined function?
You could use TEXTJOIN which is capable of ignoring blanks.
Patrick2788 opps, i translated the function name wrong from Norwegian. I use TextJoin 🙂 So TRUE is set to ignore blanks
- Geir
Are those cells really empty or are they maybe 0s formatted to display as blank? I'd check the value displayed in the formula bar.
One way to pull out the 0s in the joined string (Small example, change the ranges accordingly) :
=SUBSTITUTE(TEXTJOIN(" - ",TRUE,A1:C1),"0 -","")
