Formatting Column to Display Certain Number of Digits
Hi all! Happy New Year :) Hoping for some help with formatting numbers…
For context: Employee ID #s at my company are all 8 digits, so if someone's ID # is 123456, we add leading 0s to the front so it would be "00123456" making all Employee ID #s 8-digits.
QUESTION: Is there a format-feature or formula I can use to adjust an entire column of our Employee ID #s? I need them to match exactly between sheets/columns so I can use VLOOKUP formulas, etc.
Any insight is appreciated. Thank you!
Answers
-
Hello @Elizabeth N
You can create a new Employee ID helper column and use the formula:
="00" + [Current Employee ID #]@row
and make it a column formula.
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
The challenge with adding "00" to the beginning would be if something is already 8 digits, it will now be 10 digits, and if something is 5 digits, it will still only be 7 after pre-filling.
To get a consistent 8 digits with leading zeros dynamic to the number of characters the string already is, you would use something more like:
=RIGHT("00000000" + [Employee ID]@row, 8)
-
Hi @Melissa Yamada! Thanks for the quick reply.
That would only fix the one example above, though, right (or any that are 6-digits)? They aren't all 6-digits to begin with; some are two, some are three, some are four, etc. and I would need each of them formatted to 8-digits regardless.
For example, some possibilities would be 12 (need to be formatted to 00000012), 123 (would need to be 00000123), 1234 (would need to be 00001234), and so on. Is there a "one-size fits all" formula to adjust any number to 8-digits? I hope I am explaining this right; I'm new to Smartsheet! Appreciate the help :)
-
@Paul Newcome I just saw your response. I will give it a try and report back. Thank you!
-
@Elizabeth N Happy to help. 👍️
-
@Paul Newcome That worked perfectly! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!