Can anyone help me !

Hyaden HY01
Hillary HI01
Hilman HI02
Hilcrest HI03
Hyath HY02

I want a function to find the next first 2 charecters and give incremental
number with format of zero zero (ok the names are not repeating, they are
unique)

Now i pic first 2 letters to another column, countif then put incremental
number

=mid(a3,1,2) in C3
=countif(c$3:c3,c3) in d3
=c3&text(d3,"00")

is there any function (single or compound) to get HY02 without wasting 2
columns

Thanks in advance