Auto-numbering for Document Numbers based on word syntax (Document Register)

Hi there,

I'm trying to create a formula which picks the first 3x letters of the column, returns and adds "-" inbetween. Translating over from Excel to Smartsheet.

Current formula in Excel to translate [ =IF(B3&D3="","",(B3&"-"&D3&"-"&IF(E3="","",E3&"-")&(TEXT(K3,"0000")))) ]

I Ideally would like it to pick up and return the following in my Document Number section

If someone can help me!

Find more posts tagged with

Accepted answers

HeatherD.

Hi @Tiahna!

Based on your screenshot and the formula you provided, here's what I came up with:

=IF(AND(Department@row = "", Type@row = ""), "", UPPER(LEFT(Department@row, 3) + "-" + LEFT(Type@row, 3) + "-" + IF([Additional Topic]@row = "", Number@row, [Additional Topic]@row + "-" + Number@row)))

Smartsheet uses the plus sign instead of the ampersand. Rather than creating helper columns to pull the first three characters of the words and capitalize them, I worked in UPPER and LEFT to the formula itself.

Best,

Heather

All comments

HeatherD.

Hi @Tiahna!

Based on your screenshot and the formula you provided, here's what I came up with:

Best,

Heather

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.