2

So if I type in the following data:

A row of cells from AAA to AAC

I want to be able to drag the handle in the bottom right to expand the list all the way to ZZZ if necessary. By default, excel just repeats the sequence I typed.

Improve this question
1
  • 1
    There are not enough columns to make it all the way to ZZZ. There are 17576 combinations and only 16384 columns. – Scott Craner Apr 19 '17 at 19:32
7

Change your data to rows instead of columns, there are not enough columns to get to ZZZ.

=CHAR(INT((ROW(1:1)-1)/26^2)+65)&CHAR(MOD(INT((ROW(1:1)-1)/26),26)+65)&CHAR(MOD((ROW(1:1)-1),26)+65)

![enter image description here

Improve this answer
6
  • Excellent and robust. I think you can just use ROW() without an argument and it will assume the current cell. – Engineer Toast Apr 19 '17 at 19:48
  • @EngineerToast yes, but that assumes that the OP will put it in row 1. This way if another comes later and wants to start in row 2 it will not start at AAB – Scott Craner Apr 19 '17 at 19:49
  • Good point. Your answer is certainly better. – Engineer Toast Apr 19 '17 at 19:50
  • Thanks. How did you collapse the rows like that, by the way? – Tharwen Apr 19 '17 at 21:11
  • "Freeze panes", I assume. – Martin Argerami Apr 19 '17 at 22:20
2

Assuming you have Excel 2007 or later, start in column A with the formula:

=MID(ADDRESS(1,702+COLUMN()),2,3)

This just takes advantage of the fact that column 703 is AAA. If you start in a different column, adjust the value of 702 as needed.

Improve this answer
4
  • This will only go to XFD before throwing errors. – Scott Craner Apr 19 '17 at 19:42
  • @ScottCraner That's true but, as you pointed out above, you can only get to YGD (I think) before it breaks on any method using Address. – Engineer Toast Apr 19 '17 at 19:45
  • using Address it will only go to XFD as there are no more columns beyond that. There is no column XFE so it will be an error #Value after that. Everything after column WED will be an error. – Scott Craner Apr 19 '17 at 19:48
  • 1
    If you append AA to the left of the address and return the 3 right characters, you can start with column A and, thus, get 702 addresses further which I think is YGD. It's still a failure, it's just a failure later on. – Engineer Toast Apr 19 '17 at 19:49
1

In A1 write AAA In B1 write the following:
=SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))+1),"$",""),"1","")
and drag it in the same row to C1, D1 till you have the result you need
The formula

  • read the cell before it (left column same row) with indirect
  • Add 1 to the column
  • Substitute $ with ""
  • Substitute 1 with ""

Address Function return =Address(1,1) -->$A$1

From SUPERUSER QUESTION 867965 answered by SeanC

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.