Help with IF/Aggregate formula that finds next number/letter sequence

[willow1985]

I have the below formula that finds the next number letter sequence in column A of sheet "Batch Log" depending on which letter is selected in cell J2

For example:
if letter "A" is selected the next sequence from "Batch Log" it would give is A137
if letter "B" is selected, the next sequence the formula would give would be B1847
"C" is C611 and so on

There is one problem, the number next sequence for "D" needs to be D067 however the formula returns D67 and skips the 0.

This formula has been working great for me but I couldn't help but be curious if there was a way to account for the "0" for "D"

Current formula:

=IF(J$2="","",J$2&AGGREGATE(14,6,MID('Batch Log'!A$1:A$10000,2,9)/(LEFT('Batch Log'!A$1:A$10000,1)=J2),1)+1)

Any ideas would be appreciated

Thank you

Carla

 

[Peter_SSs]

Does this solve the problem?

=IF(J$2="","",J$2&TEXT(AGGREGATE(14,6,MID('Batch Log'!A$1:A$10000,2,9)/(LEFT('Batch Log'!A$1:A$10000,1)=J2),1)+1,"000"))

 

[willow1985]

Sorry for the late response, this works perfect!

Thank you so much ? ?

 

[Peter_SSs]

You're welcome. (Better late than never. )