Is there a way to create a 3-digit padding for numbers

Hi all, and thanks in advance!

I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3 digits and make the change if it is a 2dig. So TDL-17 will become TDL-017 and TDL-67 will become TDL-067. However no changes will happy to the Account numbers that are already 3 digits.

Find more posts tagged with

Formulas

Pad

Accepted answers

Carson Penticuff

This will also take into account any accounts that are only one digit, just in case. Adjust column names accordingly.

=IF(LEN([Original Account Number]@row ) = 5, LEFT([Original Account Number]@row , 4) + "00" + RIGHT([Original Account Number]@row , 1), IF(LEN([Original Account Number]@row ) = 6, LEFT([Original Account Number]@row , 4) + "0" + RIGHT([Original Account Number]@row , 2), [Original Account Number]@row ))

All comments

Carson Penticuff

This will also take into account any accounts that are only one digit, just in case. Adjust column names accordingly.

Sherry Fox

@Carson Penticuff ,

This is EXACTLY what I wanted! It works perfectly, thanks so much!!!! I used the format of your formula in the left column, and my raw data is in the right column. Perfect result. Thanks so very 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.