Can Excel "Autonumber" using the Alphabet?
 

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?

Can Excel "Autonumber" using the Alphabet?
 

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]
-------------------------

"jgraves" wrote in message ...
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?

Can Excel "Autonumber" using the Alphabet?
 

On Mar 21, 2:08 am, jgraves wrote:
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)

--
Regards,
Mladen
http://excelancije.bloger.hr

Can Excel "Autonumber" using the Alphabet?
 

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!

"macropod" wrote:

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]
-------------------------

"jgraves" wrote in message ...
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?

Can Excel "Autonumber" using the Alphabet?
 

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]
-------------------------

"jgraves" wrote in message ...
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!

"macropod" wrote:

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]
-------------------------

"jgraves" wrote in message ...
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?

Can Excel "Autonumber" using the Alphabet?
 

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

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

Copied down to A256


Gord Dibben MS Excel MVP

On 20 Mar 2007 18:31:22 -0700, "Mladen_Dj" wrote:

On Mar 21, 2:08 am, jgraves wrote:
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)