How do I auto format data so that the middle letter changes. EG 2.

  • Thread starter Thread starter pc..
  • Start date Start date
P

pc..

I am trying to set up an exel spread sheet so that I can change the data by
dragging down instead of re-typing each one, so for example I want accross
the top to be: 21A01 21A02 21A03 etc which I can just drag and it will
automatically change the number digit but I am trying to change the down
collum to read
21A01
21B01
21C01 etc etc
 
If your data starts in row 1, you could use a formula like:

="21"&char(row()+64)&"01"
(it'll be good for about 26 cells <vbg>.)

=char(65) is the A character.

So if you're starting on row 32, you could use:
="21"&char(row()+64-32+1)&"01"

I'd convert it to values right after I filled the range.
 
Paste this formula and drag it for the remaining cells.

="21"&CHAR(CODE("A")+ROW(Z1))&"01"

Remember to Click Yes, if this post helps!
 
If you want more than 26 letters drag this down column A

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

Will return 256 letters in 2003


Gord Dibben MS Excel MVP
 
Back
Top