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
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!