Excel 2003 -??

[MJ]

I am trying to use the Auto-Numbering feature but with alphabets:

what I need; to auto add the alphabet when i drag scroll down so it should
look like this:
12345A
12345B
12345C

Currently i just keep getting a copy of the first cell
12345A
12345A etc,.

any suggestions or ideas if that is possible in excel 2003

 

[Roger Govier]

Hi

You can't do it with Autonumbering, but the following formula should work.
This assumes you want to cycle through A to Z, then step the number up by 1,
and repeat the process.
=SUBSTITUTE(12345+INT(ROW(A26)/26)
&CHAR(MOD(ROW(A26),26)+64),"@","Z")

Once you have entered the formula in a cell and copied down as far as you
want, then copy the whole range>Paste Special>Values.

 

[HpyTrvlr69]

Thats right Jim. However, you could be creative depending upon your need.
Here is a possibility that requires a little setup:
in a blank worksheet -

at cell A6, type formula '=ADDRESS(ROW(),column(),4,1,"12345")
at cell A7, type formula '=MID(A6,2,LEN(A6)-2)
at cell A8, type formula '=LEFT(A7,5)&MID(A7,8,LEN(A7)-6)

Once you have that setup, select all three cells and drag copy across them
across spreadsheet horizontally as far as you need your alpha characters to
go. Once you have that, you can Copy, Paste Special Values with the
'Transpose' checkbox selected at any spot within your workbook and you will
have your rows.

As you can see, there really is no simple automatic way. BTW, this will work
in any version of Excel. -- HTH --

MikeB

 

[Jim Cone]

Another (weird?) way...
Enter this formula in every cell in row 1...
=ADDRESS(ROW(),COLUMN(),4)

Enter this formula in any cell in row 5 and fill down...
=12345&LEFT(OFFSET($A$1,0,ROW()-5,1,1),(ROW()-31>0)+1)

It is good for 256 entries: 12345A thru 12345IV
--
Jim Cone
Portland, Oregon USA



"MJ"
<[email protected]>
wrote in message
I am trying to use the Auto-Numbering feature but with alphabets:
what I need; to auto add the alphabet when i drag scroll down so it should
look like this:
12345A
12345B
12345C

Currently i just keep getting a copy of the first cell
12345A
12345A etc,.
any suggestions or ideas if that is possible in excel 2003