Can Excel "Autonumber" using the Alphabet?

[jgraves]

I am using Excel 2003 on Windows XP Professional.
I have a spreadsheet that has a vertical list of values.
I would like to place the letter "A" in the cell immediately to the left of
the first value, then fill the cell below that with "B" and so forth all the
way down to "DA".
I know how to do this with numbers, but can this be done with letters?

 

[macropod]

Hi,

I don't think you can do that with autofill. However, here's a formula to do it for you:
=IF(INT((ROW()-1)/26)=0,"",CHAR(64 + INT((ROW() -1)/ 26))) & CHAR(64 + MOD(ROW()-1,26)+1)
Simply put it in row 1 & copy down as far as needed. It'll cope with up to 702 rows (ie to 'ZZ').

Cheers

 

[Mladen_Dj]

I am using Excel 2003 on Windows XP Professional.
I have a spreadsheet that has a vertical list of values.
I would like to place the letter "A" in the cell immediately to the left of
the first value, then fill the cell below that with "B" and so forth all the
way down to "DA".
I know how to do this with numbers, but can this be done with letters?

Not out of box, but you can create custom list. First you must enter
all list elements in cells, than select range with list and go to
Tools-> Options -> Custom lists. Select range with your list, and next
time you can use this list as autonumber feature.

Alternative way, you can use ADDRESS function which produce cell
reference as text, and extract alphabetic part, which is column header
actually:

=LEFT(ADDRESS(1;ROW();4);FIND(1;ADDRESS(1;ROW();4))-1)

 

[jgraves]

Thank you so much! It works wonderfully.
After I have all the letters I want, I just copied them, and did a "paste
special" with "values" selected, so I can remove the formula.
Someday I will analyze the formula and see if I can understand it, but for
now - I am happy!

Hi,

I don't think you can do that with autofill. However, here's a formula to do it for you:
=IF(INT((ROW()-1)/26)=0,"",CHAR(64 + INT((ROW() -1)/ 26))) & CHAR(64 + MOD(ROW()-1,26)+1)
Simply put it in row 1 & copy down as far as needed. It'll cope with up to 702 rows (ie to 'ZZ').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am using Excel 2003 on Windows XP Professional.
I have a spreadsheet that has a vertical list of values.
I would like to place the letter "A" in the cell immediately to the left of
the first value, then fill the cell below that with "B" and so forth all the
way down to "DA".
I know how to do this with numbers, but can this be done with letters?

 

[macropod]

Glad to be of help. Your next challenge (once you've figured out how the formula works) is to extend it to cope with anything up to
'ZZZ' - which is 18,954 rows' worth. Quite easy once you know how it works.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Thank you so much! It works wonderfully.
After I have all the letters I want, I just copied them, and did a "paste
special" with "values" selected, so I can remove the formula.
Someday I will analyze the formula and see if I can understand it, but for
now - I am happy!

Hi,

I don't think you can do that with autofill. However, here's a formula to do it for you:
=IF(INT((ROW()-1)/26)=0,"",CHAR(64 + INT((ROW() -1)/ 26))) & CHAR(64 + MOD(ROW()-1,26)+1)
Simply put it in row 1 & copy down as far as needed. It'll cope with up to 702 rows (ie to 'ZZ').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am using Excel 2003 on Windows XP Professional.
I have a spreadsheet that has a vertical list of values.
I would like to place the letter "A" in the cell immediately to the left of
the first value, then fill the cell below that with "B" and so forth all the
way down to "DA".
I know how to do this with numbers, but can this be done with letters?

 

[Gord Dibben]

And one more..........

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

Copied down to A256


Gord Dibben MS Excel MVP