Autofill A B C D...AA AB...

[cmendes]

Hello,

I remember that excel could complete a sequence is we gave it the first elements.
In this case I'm writing in three different cells (same column) A, B C and I want excel to do the rest as I drag it down: D E F and so on. But if do it I get A B C again...
Is there anything that i have to do??
I'm using Excel for mac 2011
Thanks in advance,
C

 

[Rick Rothstein]

You could put =CHAR(64+ROW(A1)) in the "A" cell and drag down, then copy paste values.

Or a little more simple...

=CHAR(ROW(A65))

although as Mike points out, after Z comes [ which is probably not wanted.

 

[cafeannalisa]

Welcome to MrExcelforum @cafeannlisa

@Jonmo1's formula works without modification.

@mikerickson's formula would require modification to :-

=LEFT(ADDRESS(1, ROW(A1), 4, TRUE), (ROW(A1)>26)+(ROW(A1)>702)+1)

hth

wow.. perfect ands quick. many thanks. btw i need this to easily identify which columns to select for downloading in particular analyses

 

[JoeMo]

If you can live with a limit of 16,384 letters, this fills A1:A16384 with A,B,....,XFD. Runs in about 0.8 seconds on my computer.

Sub AutoFillAlpha()
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 16384
    Range("A" & i) = ColumnNumberToLetter(i)
Next i
Application.ScreenUpdating = True
End Sub
Function ColumnNumberToLetter(ColumnNumber As Long) As Variant
    If ColumnNumber > 16384 Then
        ColumnNumberToLetter = CVErr(xlErrNA)
        Exit Function
    End If
    ColumnNumberToLetter = Split(Columns(ColumnNumber).Cells(1).Address(1, 1), "$")(1)
End Function

 

[ravblum]

Sequential letters is not in that list. (What comes after Z, AA or [ ?)

You could put =CHAR(64+ROW(A1)) in the "A" cell and drag down, then copy paste values.

Thanks for this very helpful tip!

You can keep going after Z by inserting =CHAR(64+ROW(A$1))&CHAR(64+ROW(A1)). After AZ, change to =CHAR(64+ROW(A$2))&CHAR(64+ROW(A1)), etc.

I made a series of CONCATENATE formulas by transposing this, pasting the table into MS Word, Converting to text separated by commas, then replacing the commas by a row number and comma (eg. repl. "," with "3,").

 

[Learn14]

My Questions is...
Why do you want this?
Are you planning on using these in a formula to reference Cells later on?

There are easier ways, can you explain your larger goal?


But anyway, try this

=SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")

Your work is great but this is work only up to XFD character it should be work up to ZZZ, can you revise it?

 

[Rick Rothstein]

Your work is great but this is work only up to XFD character it should be work up to ZZZ, can you revise it?

Not as compact, but this formula will work up to ZZZ (Row 17602 if you start your numbering at Row 1)...

=IF(ROWS($1:1)>702,CHAR(65+MOD(INT((ROWS($1:1)-1)/676)-1,26)),"")&IF(ROWS($1:1)>26,CHAR(65+MOD(INT((ROWS($1:1)-1)/26)-1,26)),"")&CHAR(65+MOD(ROWS($1:1)-1,26))

 

[Tetra201]

@Rick Rothstein:

Two things with your formula:
- On row 18253, it returns "AZA' instead of "ZZA"
- It hangs Excel when attempted to autofill rows 1 to 18278 -- tested on 2 machines -- remedied by using ROWS(A$1:A1) instead of ROWS($1:1)

 

[Tetra201]

Your work is great but this is work only up to XFD character it should be work up to ZZZ, can you revise it?

Here is my take on it -- provides letter strings up to "ZZZZ":

=SUBSTITUTE(CHAR((ROWS(A$1:A1)>18278)*(MOD(FLOOR(ROWS(A$1:A1)-27,17576)/17576-1,26)+1)+64)&CHAR((ROWS(A$1:A1)>702)*(MOD(FLOOR(ROWS(A$1:A1)-27,676)/676-1,26)+1)+64)&CHAR((ROWS(A$1:A1)>26)*(MOD(FLOOR(ROWS(A$1:A1)-1,26)/26-1,26)+1)+64)&CHAR(MOD(ROWS(A$1:A1)-1,26)+65),"@","")

 

[Rick Rothstein]

@Rick Rothstein:

Two things with your formula:
- On row 18253, it returns "AZA' instead of "ZZA"

The formula I posted is supposed to be good up to ZZZ which, when placed in cell A1 and copied down stops at Row 17602 (which I now see is incorrect)... if you use the formula past that row, the displayed values would be wrong.

*** EDIT NOTE ***
Something seems wrong with my formula starting at Row 17577... it switches incorrectly from YYZ to ZZA (should switch to YZA). I'll look into this later (company is coming in about 5 minutes) and, hopefully, correct it then.

- It hangs Excel when attempted to autofill rows 1 to 18278 -- tested on 2 machines -- remedied by using ROWS(A$1:A1) instead of ROWS($1:1)

Thanks for the reminder... I always forget about that.

 

[Tetra201]

... Something seems wrong with my formula starting at Row 17577...

Actually, it stumbles earlier, at row 16901, where it returns "YZA" instead of "XZA".