# Auto Cell Increment Contain Text and Number

#### cyliyu

##### Member
I will need some help on what should be the formula to use.
I want column D to auto-increment when the date in column C filled.
19 represent 2019 and 20 represent 2020. The running number will start from 0001 when the year changed.
Thanks.

#### Attachments

• 10.6 KB Views: 8
Last edited:

#### bosco_yip

##### Excel Ninja
Try,

In D6, formula copied down :

=TEXT(C6,"ABC/RS/CAR/""""yy/")&TEXT(SUMPRODUCT(0+(YEAR(C\$6:C6)=YEAR(C6))),"0000")

Regards

#### cyliyu

##### Member
The result shows
 A62C/R0/CAR/19/0001
A62C for year 19 and A63C for 20, RS become R0 trying to understand how your formula works.

#### Attachments

• 2.1 KB Views: 11

#### deciog

##### Active Member
cyliyu

Helping

Using Bosco's solution changes this way

= "ABC/RS/CAR/"&TEXT(C6,"yy /")&TEXT(SUMPRODUCT(0+(YEAR(C\$6:C6)=YEAR(C6))),"0000")

Decio

Last edited:

#### cyliyu

##### Member
Thanks. It works now.

#### bosco_yip

##### Excel Ninja
QUOTE="cyliyu, post: 269144, member: 24854"]
.......Revision : "A62C >> year 19 , A63C >> 20 and RS become R0"......
[/QUOTE]

In D6, formula copied down :

=TEXT(RIGHT(YEAR(C6),2)+43,"A00C\/R\0\/CAR\/")&TEXT(C6,"yy/")&TEXT(SUMPRODUCT(0+(YEAR(C\$6:C6)=YEAR(C6))),"0000")