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!


Best Answer

  • HeatherD.
    HeatherD. Moderator
    Answer ✓

    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

Answers

  • HeatherD.
    HeatherD. Moderator
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!