Formatting a 'filter' for if>then>else

Archaeopteryx

Hi,

I'm attempting to use a formula filter that will prepend a character to a value if the value meets a condition.

Here is what I have:

StartsWith([ID],"8",Replace([ID], "8", "'8"))

So if [ID] starts with the character 8, I want to prepend a single apostrophe to it.

But when I run the job, my spreadsheet gets wiped. I had data before I inserted this formula, so I believe this formula to be the culprit.

Can someone tell me what I'm doing wrong?

Thanks,

jrgo

Hi @Archaeopteryx

Yes, there is an issue with your expression. The "StartsWith" function returns a logical value, or TRUE/FALSE. The first two parameters in your expression are correct, but the third is not. You're using it by nesting the "Replace" function, however, that argument is used to specify whether the lookup is case sensitive or not (default is TRUE (which is an optional parameter)). 

StartsWith(String, Target, CaseInsensitive=1)

Try this expression and see if it gets you the output you're looking for.

IF StartsWith([ID],"8")
THEN ReplaceFirst([ID], "8", "'8")
ELSE [ID]
ENDIF

I did use a different replace function as the one you were attempting to use would have replaced all "8" characters in the string, not just the first one.

Hope this helps!

Best,

Jimmy