Cannot get rid of "0" in cell

Question

Hello all,

I am trying to "EMPTY OUT" cells that have content .... but what ever I try I am left with a ZERO (0) in the cells.

I tried

* Replace with ""
* Replace with NULL()
* Change the format of the cell to varous formats
* Create new column and put results to that column

See screen shot of my formula below.

I always end up with a "0" (Excel tells me it is a Number in a Text cell).

(solution is probably very simple ... but I just cannot figure it out)

Thanks so much already

Petra

pohrt · Answer

Hi dwstada

You are correct I made a mistake when I posted my screen shot ...

But looking into this again .... after your post and starting from scrach with my format and formula fixed my case.
Thanks so much for putting in your effort!!!

pohrt · Answer

All, THANK YOU for your support.

I tried to create a simple example from the workflow I already had (to also eliminate the confidential information).

This then worked the way I wanted it ... I am not sure what happened ... I must admit ...

I rewrote the formula in my inital workflow and ended up with "BLANK" cells ... (the result I wanted)

Maybe it had something to do with the settings I had on "Peserve formatting" ....

THANKS for your interest and effort!!!

Petra

dwstada · Answer

@pohrt hey, the logic in your formulas is not doing what you think it should be doing. So you want the column "EXPLODES_NEW" to be populated based on the column "FAM", but in your formula you basically assert that something is in the column "EXPLODES" by using statements like [EXPLODES]=Null().

I think you are trying to say with this, that "EXPLODES_NEW" should be null, right?

E.g. Startswith([FAM],"1") THEN [EXPLODES]=Null() is telling Alteryx:

if "FAM" starts with a 1, then the column "EXPLODES" has to equal null (as in, in the column it has to say null), but this statement is false, because there is something other than null in the "EXPLODES" column already (e.g. empty, 1B9ZH, etc).

It is not saying that "EXPLODES_NEW" should be null and this is why you get a "0" in those cells (0 -> false, 1 or -1 -> true)

So you need to change your formula to:

IF [FAM]="000" THEN ""

ELSEIF

STARTSWITH([FAM],"1") THEN Null()

ELSEIF

STARTSWITH([FAM],"3") THEN Null()

ELSEIF

STARTSWITH([FAM],"5") THEN Null()

ELSE [EXPLODES] ENDIF