I need to replace a text string with 0 in an array

[GreasySpot]

I have an array: {"OFF", "OFF",20,30,2,1}
I need the "OFF" to be 0 so I can use it in functions.

Is this possible?

 

[Comfy]

This should be entered as an array formula Ctrl+Shift+Enter
{} Brackets will be added automatically by excel

=if(isnontext({"off","off",20,30,2,1}),{"off","off",20,30,2,1},0)

={0,0,20,30,2,1}

 

[Comfy]

This works too:

=IFERROR(--{"OFF","OFF",20,30,2,1},0)

 

[GreasySpot]

Any way to make negative numbers 0 in the same array as above?

 

[Comfy]

One way would be to do this:

=IF(IFERROR(--{"OFF","OFF",20,30,2,-1},0)<0,0,IFERROR(--{"OFF","OFF",20,30,2,-1},0))

 

[GreasySpot]

Thanks Man! I was able to work everything out. Thanks a TON!

 

[pgc01]

Hi

Another option:

=0+TEXT(A1,"0;\0;0;\0")