Using IF(LEN) to add proceeding zeros to a number based on length

Dan Y

I want to add a set number of zeros to an employee ID based on the employee ID length to make all IDs 7 digits.

Some long tenured employees only have a 4 digit employee ID where as a newly hired employee have a 7 digit ID. Ex. Employee 0002709 vs New Employee 0148749. All employee IDs are entered without the preceding zeros, but for comparison purposes, I need another column with the preceding zeros as well so I want to add the extra zeros.

The individual components of the below formula return expected results, but when I combine them, I get a #UNPARSABLE error. What am I doing wrong?

=IF(LEN([Employee ID]@row) = 4, "000" + [Employee ID]@row, IF(LEN([Employee ID]@row) = 5, "00" + [Employee ID]@row), "0" + [Employee ID]@row))))

JamesB

@Dan Y

Took out some parentheses and the error went away. Note if your Employee ID is less than 5, it will add 1 zero to the number with this formula.

=IF(LEN([Employee ID]@row) = 4, "000" + [Employee ID]@row, IF(LEN([Employee ID]@row) = 5, "00" + [Employee ID]@row, "0" + [Employee ID]@row))

JamesB

@Dan Y

Took out some parentheses and the error went away. Note if your Employee ID is less than 5, it will add 1 zero to the number with this formula.

=IF(LEN([Employee ID]@row) = 4, "000" + [Employee ID]@row, IF(LEN([Employee ID]@row) = 5, "00" + [Employee ID]@row, "0" + [Employee ID]@row))