Evaluate a current field based on multiple conditions and output a new column

[Zakir]

Hello,

I need a formula that evaluates "current field" based on its 1st *AND 1st two digits below. For example if current field starts with 2 then I want B, if it starts with 51 then C and if it starts with 52 then C. Is there a way to do it using 1 tool or do I have to use multiple tools/actions to address this?

Current Field	Condition	Result
2122	Starts with 2	B
2955	Starts with 2	B
51	Starts with 51	C
52	Starts with 52	D

[Luke_C]

Hi @Zakir 

A formula tool with an IF statement should accomplish this, the below formula would get the logic. Your current field would need to be converted to a string if it isn't already.

if left([Current Field],1) = '2' then 'B'
elseif left([Current Field],2) = '51' then 'C'
elseif left([Current Field],2) = '52' then 'D'
else '' endif

[Jonathan-Sherman · Accepted solution]

Hi @Zakir,

I'd probabbly take the approach of a Find and Replace tool. you'd need to convert the values to strings first (but could always be changed back after) to append results from a lookup table. It also saves you from having to write out and look after a long IF formula which looks at a different number of starting characters (in your example the number of starting characters could be one or two) and keeps it more structured in the process:

For example, my lookup table looks like this:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Kind regards,

Jonathan

[Zakir]

@Jonathan-Sherman This worked as expected. I only made one small change to Find replace tool i.e. I changed the find to Beginning of Field.

Thank you!

Zak