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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!