Using Countif in place of Autonumber - formatting question!

JBS27

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., "IT-1") and I'd like it to be numbers to appear with 3 digits (I.e., "IT-001")

Is there a way to format the formula to increase the number of digits shown in the formula's results?

Mike TV

@JBS27

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-", "IT-00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT-0", "IT-00"), [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.

Mike TV

@JBS27

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-", "IT-00"), IF([Column3]@row = 5, SUBSTITUTE([Column2]@row, "IT-0", "IT-00"), [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.