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

Tiahna

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!

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

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