Using Countif in place of Autonumber  formatting question!
I'm creating a ticketing system with multiple prefixes. Because of the multiple prefixes on one sheet, II opted to create a nested formula to number the tickets based on the various types of requests. Here is the formula:
=VLOOKUP([Request Type]@row, {Ticket Assignment}, 2, false) + (COUNTIFS([Request Type]$1:[Request Type]@row, [Request Type]@row, Created$1:Created@row, YEAR(@cell) = YEAR(Created@row)))
The problem I'm having is that I'm getting tickets with a single digit (I.e., "IT1") and I'd like it to be numbers to appear with 3 digits (I.e., "IT001")
Is there a way to format the formula to increase the number of digits shown in the formula's results?
Best Answer

I'm sure there's more than one way to accomplish this. Here's my shot at it:
First you set up a helper column such as the one I have in Column3 with this formula which tells you the length of the value in Column2:
=LEN([Column2]@row)
Then you set your final result formula such as the one I have in Column4 like so:
=IF([Column3]@row = 4, SUBSTITUTE([Column2]@row, "IT", "IT00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT0", "IT00"), [Column2]@row))
If you end up with any rows that do not have a properly formatted number, let me know and we can work out the bugs. After it's all set up, you can hide Column2 and Column3 on the sheet.
Answers

I'm sure there's more than one way to accomplish this. Here's my shot at it:
First you set up a helper column such as the one I have in Column3 with this formula which tells you the length of the value in Column2:
=LEN([Column2]@row)
Then you set your final result formula such as the one I have in Column4 like so:
=IF([Column3]@row = 4, SUBSTITUTE([Column2]@row, "IT", "IT00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT0", "IT00"), [Column2]@row))
If you end up with any rows that do not have a properly formatted number, let me know and we can work out the bugs. After it's all set up, you can hide Column2 and Column3 on the sheet.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!