numbering sequence

[valley1]

Hi all,

I have the following set of characters in a cell

CCC01/23333

and I wish to drag the cell down so only the first set of numbers change as below, however, when I drag the cell down the last set of numbers change.

Does anybody know how I can fix this?

CCC01/23333
CCC02/23333
CCC03/23333
CCC04/23333
CCC05/23333
CCC06/23333

 

[Aladin Akyurek]

Book16
	A	B	C	D
1	Item
2	CCC01/23333
3	CCC02/23333
4	CCC03/23333
5	CCC04/23333
6	CCC05/23333
7	CCC06/23333
8	CCC07/23333
9	CCC08/23333
10	CCC09/23333
Sheet1

A2:

="CCC"&TEXT(COUNTA($A$1:A1),"00")&"/23333"

Select the range from A2 on, activate Edit|Copy, then activate Edit|Paste Special >Values.

 

[onlyadrafter]

Hello,

Try this formula in A2 if A1 contains CCC01/23333.

=LEFT($A$1,4)&MID(A1,5,1)+1&MID($A$1,6,6)

What happens after CCC09/23333?

 

[Legacy 21301]

Another way :

Assuming that CCC01/23333 is in a cell in row 3, re-enter as ="CCC"& TEXT(ROW()-2,"00") & "/23333" and fill down.

 

[valley1]

Another way :

Assuming that CCC01/23333 is in a cell in row 3, re-enter as ="CCC"& TEXT(ROW()-2,"00") & "/23333" and fill down.

Nice one,

that works however I forgot to mention that my CCC number starts at

CCC6771/23333

and needs to be dragged down as follows:

CCC6771/23333
CCC6772/23333
CCC6773/23333
CCC6774/23333
CCC6775/23333
etc....

Can the formula be updated to do this?????

 

[Andrew Poulsom]

Just amend this bit:

TEXT(ROW()-2,"00")

to eg:

TEXT(ROW()+6770,"0000")

if your data starts is row 1.

 

[BuddieB]

that works however I forgot to mention that my CCC number starts at
CCC6771/23333
and needs to be dragged down as follows:
CCC6771/23333
CCC6772/23333
CCC6773/23333
CCC6774/23333
CCC6775/23333
etc....

Can the formula be updated to do this?????

You might try:

If you start in row 3, ="CCC"& TEXT(6770+ROW()-2,"00") & "/23333" and fill down.