Convert excel formula to Alteryx formula

[caiovidal22]

Hi there,

Im trying to use the formula I used on excel, on a alteryx workflow, but the language is somewhat different.

how can I translate this formula, to alteryx language? 

=IF(LEN(TEXT(NUMBERVALUE(MID(Q2,SEARCH("4",Q2),7),"."),"0"))=7,NUMBERVALUE(MID(Q2,SEARCH("4",Q2),7)),"")

thankyou all!

[JulioMO]

Hi @caiovidal22 

Could you please put an example of what you want to achieve, with the input data you have and which output you want. I think it would be easier to help you that way.

Regards

[PeterA1]

Do you have a sample attachment of the original data and what you are trying to achieve? That might help. It is a bit difficult to interpret an excel formula without seeing the reference cells. If so, I can take a look

[RolandSchubert · Accepted solution]

Hi @caiovidal22 ,

I think, it should be:

IIF(Length(ToString(ToNumber(Substring([Field1], FindString([Field1], '4'), 7), 0, 1, '.'))) = 7, ToNumber(Substring([Field1], FindString([Field1], '4'), 7), 0, 1, '.'),'')

I've attached a workflow, so you can better identify the "translation".

Best

Roland

[CharlieS · Accepted solution]

Here's a list of corresponding functions:

Excel -> Alteryx

LEN( -> Length(

TEXT( -> ToString(

NUMBERVALUE( -> ToNumber(

MID( -> SubString(

SEARCH( -> FindString(

Looks like @RolandSchubert nailed it.