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

 

[shg]

ZZZZZZZZZZ corresponds to 146813779479510

 

[FlameRetired]

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,"")

Jonmo1 I know this is an old thread, but I have been looking for this solution for over a year. It turns out to be so simple. Aren't they usually?

Anyway thanks.

 

[Jacra]

You could try this UDF approach
Put "A" in A1 (no quotes) and =NEXTSTRING(A1) in A2 and drag down as needed.

Function NextString(ByVal aString As String) As String
    Dim suffix As String
    If aString = vbNullString Then
        NextString = "A"
    ElseIf Right(aString, 1) = "Z" Then
        NextString = NextString(Left(aString, Len(aString) - 1)) & "A"
    Else
        Mid(aString, Len(aString), 1) = Chr(Asc(Right(aString, 1)) + 1)
        NextString = aString
    End If
End Function

this is great.

 

[Beth A]

If you want A, B, C, ..., Y, Z, AA, AB, ... you could use the formula

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

Jonmo1, I like the Substitute approach.

THANK YOU!!!!!!!!!!! You are my hero!

 

[basilafj]

Hello all,
I want also to give unique ID starting from AA0001 to ZZ9999
means after AA9999 it should go to AB0001
please help

 

[mikerickson]

Perhaps

Function NextIDNumber(strCurrent As String) As String
    Dim curIndex As Long, curPrefix As String
    Dim nextIndex As String, nextPrefix As String
    curIndex = Int(Val(Right(strCurrent, 4)))
    curPrefix = UCase(Left(strCurrent, 2))
    
    nextIndex = Right("00000" & (curIndex + 1), 4)
    If nextIndex = "0000" Then
        If Right(curPrefix, 1) = "Z" Then
            nextPrefix = Chr(Asc(Left(curPrefix, 1)) + 1) & "A"
        Else
            nextPrefix = Left(curPrefix, 1) & Chr(Asc(Right(curPrefix, 1)) + 1)
        End If
        nextIndex = "0001"
    Else
        
        nextPrefix = curPrefix
    End If
    NextIDNumber = nextPrefix & nextIndex
End Function

 

[basilafj]

Thank you,
But I can’t do it without code?
Mean using excel only?

 

[Rick Rothstein]

But I can’t do it without code?

Put AA0001 in cell A1, then put this formula in cell A2 and copy it down...

=CHAR(CODE(A1)+(RIGHT(A1,5)="Z9999"))&IF(RIGHT(A1,5)="Z9999","A",CHAR(CODE(MID(A1,2,1))+(RIGHT(A1,4)="9999")))&IF(RIGHT(A1,4)="9999","0001",TEXT(RIGHT(A1,4)+1,"0000"))

Note: The last cell in Column A for my system (A1048576) will contain the code EA8680 so you will never get anywhere near ZZ9999.

 

[basilafj]

Thank you very much for the formula, I will try it soon.
For for completing to ZZ9999 can continue in another sheet ?

 

[Rick Rothstein]

Put EA8681 (the next code) in cell A1 of that other sheet, then put the formula I posted in cell A2 on that sheet and copy it down.