numbering sequence

valley1

New Member
Joined
Nov 7, 2003
Messages
6
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


:oops:
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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?
 
L

Legacy 21301

Guest
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

New Member
Joined
Nov 7, 2003
Messages
6

ADVERTISEMENT

Ponsy Nob. said:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Just amend this bit:

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

to eg:

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

if your data starts is row 1.
 

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
valley1 said:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top