ID number conversion

Question

I have a staff ID list with maybe 4 or 5 or 6 digit, I want to convert this number to a 9 digit account number, starting with "E".

If staff ID is 4 digit, account number is E + 0000+ staff ID

If staff ID is 5 digit, account number is E + 000+ staff ID

If staff ID is 6 digit, account number is E + 00+ staff ID

Which tool and formular is better to get this?

ID code conversion.xlsx

Pilsner · Answer

Hello, I have tried to solve your problem using a formula tool. There is a step in there which will return a null value if the original number is not 4,5 or 6 digits just to make sure you can identify anomalies like this if they did occur. The select tool is there to ensure the original ID column is stored as a V_string. I hope this helps

Creating account number.yxmd

Yoshiro_Fujimori · Answer

Hi @Shaopingfu ,

Please try this expression.

Account Number = "E" + PadLeft(ToString([Employee ID]), 8, "0")

I hope this helps

PadLeft.yxmd