If Null formula

[JingYih_Herbalife]

Would like to have a simple formula whereby if the column has a number, then the result would be "Y", otherwise it would stay as null or empty. 

Please help. 

[flying008]

Hi, @JingYih_Herbalife 

You can try this formula:

IIF(ToNumber([Txt]), 'Y', [Txt])

[ahsanaali]

See if this works for you.

[ArnaldoSandoval]

Hi @JingYih_Herbalife 

Be aware that ToNumber([txt]) returns NULL for values like 'AB12' and 'Y' when the value is '12AB'

I attaching my test workflow:

hth

Arnaldo

[flying008]

Hi, @ArnaldoSandoval 

Yep, you are right ! ToNumber() only for pre number.

[binu_acs]

@JingYih_Herbalife Another option with Regex_Match function

[ArnaldoSandoval]

@JingYih_Herbalife  @binu_acs 

If the input data field contains leading or trailing spaces, the REGEX_Match() failed to identify ' 233' or '444 ' as numeric, perhaps a Trim() can handle that.

Arnaldo

[RobertOdera]

Hi, @JingYih_Herbalife 

Consider this treatment.

IF IsEmpty(Trim(ToString([2020]))) THEN Null()

ELSE Trim(ToString([2020]))

ENDIF

IsEmpty evaluates for both nulls and blanks and you might set the outcome as Null() versus null or empty

I hope you find this helpful - Cheers!