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!

Find more posts tagged with

Formulas

Accepted answers

Elizabeth N

@Paul Newcome That worked perfectly! Thank you so much!

All comments

Melissa Yamada

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.

Paul Newcome

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)

Elizabeth N

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 :)

Elizabeth N

@Paul Newcome I just saw your response. I will give it a try and report back. Thank you!

Paul Newcome

@Elizabeth N Happy to help. 👍️

Elizabeth N

@Paul Newcome That worked perfectly! Thank you so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.