# Next letter in sequence

Hi All,

I have a report in Excel 2010 which summarises costs against certain activity charge codes. The charge codes for this particular project are sequential and always 8 alpha characters. They all start with the same 4 characters and then end with AAAA, moving on to AAAB, AAAC etc. I also use this list to indicate which activity charge code is needed next in the sequence and so far this works.

The summary list has a column that pulls the last character for each of the activities, populating a range G2:G26. I then use the following formula to determine what character I need next:

=CHAR(MAX(CODE(G2:G26))+1)  entered as an array formula.

My list of last characters is not in alphabetical order so it isn't as simple as looking at the last one in the list. The current list has reached Y so the next activity to be raised will end in AAAZ. Thats fine, my formula currently returns Z as the correct answer.

However when I go beyond Z, the next activity will have to end AABA, then AABB etc.

A sample of the set up is attached. You will see from the sample that some tasks have multiple phases (preparation and execution (P / E)) and each phase has a new activity code, hence why they are not in alphabetical order. Some tasks were raised in original group and then others have been added.

The formula that I am trying to update is highlighted. I am happy to change the formulas to pull out the existing suffixes if required, hence why I have supplied the full suite of data as best I can.

At the moment I can do the increment simply but as this project expands it will become less simple.

Thanks
Rob H
Sequential-lettering.xlsx
Microsoft Excel

Last Comment
Michael Fowler

8/22/2022 - Mon
Michael Fowler

I couldn't think of a way to do this with a formula, while that does not mean it cannot be done, here is a VBA function to do it for you

``````Function LetterIncrement(stringCounter As String) As String
Dim arr(1 To 4) As Integer
Dim i As Integer

For i = 1 To 4
arr(i) = Asc(UCase(Mid(stringCounter, i, 1)))
Next

arr(4) = arr(4) + 1

For i = 4 To 1 Step -1
If (arr(i) > 90) Then
arr(i) = 65
arr(i - 1) = arr(i - 1) + 1
End If
Next

LetterIncrement = Chr(arr(1)) & Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))

End Function
``````

Note:
I deliberately let it throw an error if "ZZZZ" is entered
Always returns upper case but will take upper or lower as an argument
Rob Henson

Thanks I will check it out when back at desk tomorrow.

Question though, this looks like it needs an input of 4 letters in a string and it will give the next string of 4. How do I determine the string of 4 to enter? I can change my list so that it shows 4 characters rather than 1 but how can I check the list for the "highest" and thus get the next.

Thanks
Ejgil Hedegaard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Michael Fowler

Here is an update.
LetterIncremented takes a range
Determines the largest value in the range
Then returns the next number with leading "A"s stripped.

It has been done by treating the string as a Base 26 number with "A" as zero.

``````Private Function Base26Max(rng As Range) As String
Dim r As Range
Dim i As Long, j As Long, currMax As Long
Dim arr(1 To 4) As Integer
Dim str As String

currMax = 0

For Each r In rng
str = Right(r.Value, 4)
j = 0
For i = 1 To 4
j = j + ((Asc(UCase(Mid(str, i, 1))) - 65) * (26 ^ (i - 1)))
Next
If j > currMax Then Base26Max = str
Next

End Function

Function LetterIncremented(rng As Range) As String
Dim arr(1 To 4) As Integer
Dim i As Integer
Dim str As String

str = Base26Max(rng)

For i = 1 To 4
arr(i) = Asc(UCase(Mid(str, i, 1)))
Next

arr(4) = arr(4) + 1

For i = 4 To 1 Step -1
If (arr(i) > 90) Then
arr(i) = 65
arr(i - 1) = arr(i - 1) + 1
End If
Next

If arr(1) = 65 Then
If arr(2) = 65 Then
If arr(3) = 65 Then
LetterIncremented = Chr(arr(4))
Else
LetterIncremented = Chr(arr(3)) & Chr(arr(4))
End If
Else
LetterIncremented = Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))
End If
Else
LetterIncremented = Chr(arr(1)) & Chr(arr(2)) & Chr(arr(3)) & Chr(arr(4))
End If

End Function
``````
Rob Henson