Forum Discussion

Hogstad_Raadgivning

Iron Contributor

Nov 06, 2019

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, I...

Formulas and Functions

Silver Contributor

Nov 06, 2019

Hogstad_Raadgivning

The issue looks to be with TEXT.COMBINE - is that a user defined function?

You could use TEXTJOIN which is capable of ignoring blanks.

Hogstad_Raadgivning
Iron Contributor
Nov 06, 2019
Patrick2788 opps, i translated the function name wrong from Norwegian. I use TextJoin 🙂 So TRUE is set to ignore blanks

- Geir
- Patrick2788
  Silver Contributor
  Nov 06, 2019
  Hogstad_Raadgivning
  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 -","")
  - Hogstad_Raadgivning
    Iron Contributor
    Nov 06, 2019
    Hi Patrick2788 Good quetsion. They are dropdownlist, with a table range. I made the cells standard cells, also changed it to a Text cell. Same result.
    
    Here with textjoin only: The function in English =Textjoin(" - ";TRUE;t_teamleder[Man48])
    No blanks. So must be the IF?
    I Will Try Substitute
    
    I tried with FILTEER. Evalueting the FILTER formula, shows 0, but they does not display.
    Function:
    =TextJoin(" - ";TRUE;FILTER(t_teamleder[Tir48];t_teamleder[AnsattID]=[@AnsattID]);FILTER(t_montasje[Tir48];t_montasje[AnsattID]=[@AnsattID]))

Resources