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

[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

 

[Dave Peterson]

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.

 

[Ms-Exl-Learner]

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!

 

[Gord Dibben]

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