Using IF(LEN) to add proceeding zeros to a number based on length
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))))
Best Answer
-
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))
Answers
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!