1, 2, 3, 4, ... 1a, 1b, 1c, ...

  • Thread starter Thread starter mkengel
  • Start date Start date
M

mkengel

By dragging the black rectangle at the bottom of a cell, you can
automatically increase a number by plus one while moving from one cell
to the next..
Is it possible in Excel to start, e.g. with 1a, and by dragging, you
can create new cells with the content 1b, 1c, 1d, ..., 1z ?

Thank you for any help.
Michael
 
You could try Tools Options and Custom lists if this is something you do
regularly

otherwise you could put A in Cell A1 and B in cell B1 then use
concatenate to join them in cell C1.

e.g =A1&B1

VBA Noob
 
mkengel said:
By dragging the black rectangle at the bottom of a cell, you can
automatically increase a number by plus one while moving from one cell
to the next..
Is it possible in Excel to start, e.g. with 1a, and by dragging, you
can create new cells with the content 1b, 1c, 1d, ..., 1z ?

Thank you for any help.
Michael
Click to expand...

Hi Michael

One way:

Rowwise.
In A1:
=1&CHAR(97+ROW()-ROW($A$1))

Copy A1 down with the fill handle.

If you start in e.g. K3:
=1&CHAR(97+ROW()-ROW($K$3))


Columnwise.
In A1:
=1&CHAR(97+COLUMN()-COLUMN($A$1))

Copy A1 to the right.

If you start in e.g. K3:
=1&CHAR(97+COLUMN()-COLUMN($K$3))
 
or

=IF(ISERROR(COLUMN()&CHOOSE(MOD(ROW(),27),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z")),"Next
Row",COLUMN()&CHOOSE(MOD(ROW(),27),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"))
 
or

=COLUMN($A$1)&CHOOSE(IF((MOD(ROW(),26))=0,26,((MOD(ROW(),26)))),"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"
 
If you want to go past 1Z try this formula.

=1&LOWER(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))

Will take you to 1iv

Don't remember who came up with this one, just passing along.


Gord Dibben MS Excel MVP
 
Back
Top