Help with applying excel formula to Knime workflow

[chimdee]

Hi

I would like to apply the below excel formula to a, integer column in my Knime work flow.

=IF(M2<>"",IF(LEN(M2)<9,IF(LEN(M2)<7,“000”&M2,IF(LEN(M2)<8,“00”&M2,“0”&M2)),M2),"")

I have a column in my excel reader file that is being read as an integer. The output I need is an 11 character string in this format: XXX-XX-XXXX. Because the column is being read as an integer it is currently dropping digits in the beginning of the string if the cell starts with a zero. Additionally, I have no idea how to add the two hyphens, so any help the community can provide on that would be much appreciated as well.

Thank you in advance!

[izaychik63]

Convert column M2 to string using String Manipulation node.
Then use Column Expression to calculate the formula
and use String Manipulation again to add dashes.

[chimdee]

Hi izaychik63,

What node is Column Expression? Searched it in the node repository but nothing came up.

[izaychik63]

You need to install it

Column Expressions — NodePit

[chimdee]

Oh I see. Ok I’ll check it out. Thank you for your help!

[ipazin]

Hi there @chimdee,

if I got you right and your problem starts with loosing first zeroes when reading Excel file check this topic.

After you sort that out you can use Column Expressions as suggested by @izaychik63 in addition to String Manipulation and/or Rule Engine node…

Check here to see how to add new nodes to KNIME.

Br,
Ivan