adding 1 to each following cells with a prefix

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

How do I automate the editing in situation such as the column content
below.

M001
M002
M003

There is prefix M00 but the cells of the rest is plus one from the
first. If it's just numbers, I know =A1 + 1 and copy-and-paste would
do it but it won't work with a prefix.

Thanks for the help,
 
Put M001 in your first cell, select that cell and then click on the small
black square in the bottom right corner of that cell's border and drag it
down as far as you want... Excel will automatically increment the number
part for you.
 
How do I automate the editing in situation such as the column content
below.

M001
M002
M003

There is prefix M00 but the cells of the rest is plus one from the
first. If it's just numbers, I know =A1 + 1 and copy-and-paste would
do it but it won't work with a prefix.

Thanks for the help,
Click to expand...

If you only require the cell to DISPLAY in the above format, you could use a
custom number format of \M000 and then use the A1+1 method.
--ron
 
I just noticed that if my prefix is just 1 letter, the number cycles
by 4, like 1,2,3,4,1,2,3,4, etc instead of 1,2,3,4,5,6,7...

For example enter Q1 at A1 and I tried to use "drag the corner" tip, I
got Q1, Q2, Q3, Q4, and then Q1 again. If Use Q01, then it's fine.
Anyway to make it work as Q1, etc.

Thanks,
 
Q1, Q2, Q3 and Q4 are one of Excel's built-in Custom Lists which does not
recognize more than 4 Quarters.

In A1 enter ="Q" & ROW()

Copy down then copy>paste special>values>ok>esc.


Gord Dibben MS Excel MVP
 
Back
Top