Continuing abnormal series in column

[MarcR10]

Hello,

I am trying to continue an abnormal series. Generally, it is easy to click and drag to continue a series in the same column, but when I am trying to do the same technique with this series, the newly filled cells simply repeat the selected cells.

I would like my series to be in the same column, as follows: EBL21-001-A, EBL21-001-B, EBL21-001-C, etc.

To clarify, if I were to type in the first three, highlight them, then drag down, the result would be a repetition of ...A,...B,...C,...A,...B,..C,...A,...B,....C. I would like it to continue as A,B,C,D,E,...,Z,AA,AB,AC, etc.

I figure that I need to format the cell(s) with a custom number format but I can not get it to work.

Thanks for the help.

[#2]

@MarcR10 

Assuming your data is in A:A, I would create a helper column to track new occurrences. In B1, try =IF(COUNTIF($A$1:$A1,A1)=1,A1,""), then in C1, =FILTER(Z:Z,Z:Z<>"").

B column will list every new code, then the formula in C will filter out all the unneeded blanks.

If you just want the last digit, create a second column using =RIGHT(A1,1) and drag down. 

[#3]

@MarcR10 

Do you mean that you want to have a auto running number, from 001 to 999?

="EBL"& RIGHT("00"&(21+FLOOR.MATH((ROW(A1)-1)/(999*3))),2) & "-" & 
 RIGHT("000"& (MOD(INT((ROW(A1)-1)/3),999)+1),3) & "-" & 
 IF(MOD(ROW(A1),3)=1,"A",IF(MOD(ROW(A1),3)=2,"B","C"))

Note:

1) you may replace 999*3 with 2997

2) after row 2997, it will be EBL22-001-A, EBL22-001-B, EBL22-001-C, ...etc 

3) copy and paste the formula to the row you need (after that, you may copy the cell and paste with text to fix the value)