convert excel function to spotfire

[Ryan K.]

I have this function in excel:

=IFERROR(RIGHT(A2,LEN(A2)-FIND("$",SUBSTITUTE(A2,"-","$",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))),A2)

It is looking into a column whose sample value is "First Name, Last Name C UYXS-MND/U/MN" and the result looks like this "MND/U/MN"

It basically extracts everything after the dash "-".

Can you please help me with what funtion should I use in a calculated column

[Jason Joslin 3]

Hi Ryan,

If your column values really only ever have one '-' then this function will work. There are other more complicated solutions available if your values are less consistent (I.e. more than one '-' or other characters).

Split([ColumnA], '-', 2)This will return just the right-hand side after the split character '-'. If you still want to copy the Excel formula and return the entire value if no '-' is found then use this:

If(Split([ColumnA],"-",2) is null,[ColumnA],Split([ColumnA],"-",2))Regards,

Jason

[Ryan K.]

Works. Thank you so much Jason.

[Damilola Adeyemi]

Hi Team,

I have been trying to get this expression working.I have been struggling with the conversion syntax in Spotfire, trying to compare a string with an integer.

Please help

Case

When Len([ComsumerID] = 5 ) then "Individual"

ELSE "Other"

End

 

Excel function and it worked

=IF(LEN(I2)=5,"Individual","Other")