Autonumbering 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

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

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
Categories
Check out the Formula Handbook template!