Increment Letter in a String

[manish]

Hi,

I am sorry if it is repost. I am relatively new to macros. I want to
increment letter in a string present in a cell. If the cell value is
"A" I want to make it "B" etc. However, if the cell value is "Z", I
want to change it to "AA". If it is "AA", it should change to "AB" and
so on. I wrote following code for increment letters:
NextIndex = Asc(char) + 1
CellOfInterest.Value = Chr(NextIndex)

Above code changes A to B, B to C and so on. However, it does not
change Z to AA. It just gives me next ASCII value ([). Is there any
way to make this happen?

Any help would be greatly appreciated!

 

[Guest]

Hi manish -

The code below will increment the index letters in your variable "char" up
to "ZZ", the maximum for a two-letter index:

Select Case Len(char)

Case 1 'char is one letter
If UCase(char) = "Z" Then
CellOfInterest.Value = "AA"
Else
CellOfInterest.Value = Chr(Asc(char) + 1)
End If

Case 2 'char is two letters
If Right(char, 1) = "Z" Then
CellOfInterest.Value = Chr(Asc(Left(char, 1)) + 1) & "A"
Else
CellOfInterest.Value = Left(char, 1) & Chr(Asc(Right(char, 1)) +
1)
End If

End Select

 

[Bernd P]

Hello,

If you like to use a UDF:

Function charinc(s As String) As String
'Increments a string:
'A will become B
'Z will become AA
'ABCZ will become ABDA
'ZZZ will become AAAA
'Reverse(moc.liborplus.www) v0.10
Dim i As Long
Dim sc As String 'Current char
Dim sp As String 'Prefix
Dim ss As String 'Suffix

sp = s
ss = ""
i = Len(s)
Do While i > 0
sc = Right(sp, 1)
If i > 1 Then
sp = Left(sp, i - 1)
Else
sp = ""
End If
Select Case sc
Case "A" To "Y"
sc = Chr(Asc(sc) + 1)
Exit Do
Case "Z"
ss = "A" & ss
If i = 1 Then
sp = "A"
sc = ""
Exit Do
End If
Case Else
charinc = CVErr(xlErrValue)
Exit Function
End Select
i = i - 1
Loop
charinc = sp & sc & ss
End Function

Regards,
Bernd

 

[manish]

Hello,

If you like to use a UDF:

Function charinc(s As String) As String
'Increments a string:
'A will become B
'Z will become AA
'ABCZ will become ABDA
'ZZZ will become AAAA
'Reverse(moc.liborplus.www) v0.10
Dim i As Long
Dim sc As String 'Current char
Dim sp As String 'Prefix
Dim ss As String 'Suffix

sp = s
ss = ""
i = Len(s)
Do While i > 0
sc = Right(sp, 1)
If i > 1 Then
sp = Left(sp, i - 1)
Else
sp = ""
End If
Select Case sc
Case "A" To "Y"
sc = Chr(Asc(sc) + 1)
Exit Do
Case "Z"
ss = "A" & ss
If i = 1 Then
sp = "A"
sc = ""
Exit Do
End If
Case Else
charinc = CVErr(xlErrValue)
Exit Function
End Select
i = i - 1
Loop
charinc = sp & sc & ss
End Function

Regards,
Bernd

Thanks a lot. It really works!

 

[Roger Govier]

Hi

Whilst you have been given answers to the question you posed, if the object
is to make up a range value to extract data from a cell e.g.
Range("AA1").value, you might find it easier to use Cells()

x= x+1 (where x was 26)
Cells(1,x).value would give the same result.

 

[Dave D-C]

'On my XL97, this routine goes up to column "IV"

Sub Demo()
MsgBox ColumnPlusOne("Z")
MsgBox ColumnPlusOne("IU")
End Sub

Function ColumnPlusOne$(pStr$)
Dim s1$
s1 = Columns(Columns(pStr).Column + 1).Address
ColumnPlusOne = Mid$(s1, InStr(s1, ":$") + 2)
End Function ' D-C Dave

I am sorry if it is repost. I am relatively new to macros. I want to
increment letter in a string present in a cell. If the cell value is
"A" I want to make it "B" etc. However, if the cell value is "Z", I
want to change it to "AA". If it is "AA", it should change to "AB" and
so on. I wrote following code for increment letters:
NextIndex = Asc(char) + 1
CellOfInterest.Value = Chr(NextIndex)
Above code changes A to B, B to C and so on. However, it does not
change Z to AA. It just gives me next ASCII value ([). Is there any
way to make this happen?
Any help would be greatly appreciated!

 

[Dana DeLouis]

If it is "AA", it should change to "AB" etc

I see you have solutions. If you would like a base-26 type of solution,
here are a few ideas:

Sub TestIt()
Debug.Print NextSequence("ZZZZZ")
Debug.Print NextSequence("ABCDEFGZ")
End Sub

Returns:
AAAAAA
ABCDEFHA

Function NextSequence(Seq As String) As String
'// Dana DeLouis
Dim p As Long
Dim k As Long
Dim x As Long
Dim s As String
Dim sol As String
Dim n As Double
Const B As Long = 26 'Base 26

s = UCase(Seq)
If Not bAllLetters(s) Then
NextSequence = "Error: Not all numbers: " & Seq
Exit Function
End If

k = Len(s)
For p = 1 To k
x = Asc(Mid$(s, k - p + 1, 1)) - 64
n = n + x * B ^ (p - 1)
Next p

n = n + 1 'Next Sequence

p = Int(Log(n) / Log(B))
Do While p >= 0
x = Int(n / 26 ^ p)
sol = sol & Chr(x + 64)
n = n - x * B ^ p
p = p - 1
Loop
NextSequence = sol
End Function

Private Function bAllLetters(s As String) As Boolean
Const p As String = "[A-Z]"
bAllLetters = UCase(s) Like WorksheetFunction.Rept(p, Len(s))
End Function

 

[Bernd P]

Hello Dana,

Your macro takes 50% more runtime than mine (FastExcel says) and it
does not work for ZZZZZZZZZZZZ or longer strings. But maybe we should
even restrict our solutions to IV

Regards,
Bernd

 

[Dana DeLouis]

Hi Bernd. Thanks for the catch. You're right. I see the logic error.
Most of the time, the last character is not "z." Your code logically exits
the function much earlier. My code unnecessarily keeps working. :>~

This code is not any better, and may be slower than your excellent code, but
was something I was messing around with.

Function IncChar(Str) As String
Dim s As String
Dim C As String 'Character
Dim p As Long ' Loop Counter
Dim CF As Boolean 'Carry Flag
Const Z As String = "Z"
Const A As String = "A"

s = UCase(Str)
If Not s Like WorksheetFunction.Rept("[A-Z]", Len(s)) Then
IncChar = "#N/A"
Exit Function
End If

'// Most Common - does not end in Z
If Not (s Like "*Z") Then
Mid(s, Len(s), 1) = Chr(Asc(Right$(s, 1)) + 1)
Else
CF = True
For p = Len(s) To 1 Step -1
C = Mid$(s, p, 1)
If C = Z Then
Mid(s, p, 1) = A
Else
If Not CF Then Exit For
C = Chr(Asc(C) + 1)
Mid(s, p, 1) = C
End If
CF = (C = Z)
Next p
If CF Then s = A & s
End If
IncChar = s
End Function

 

[Bernd P]

Hello Dana,

Now your code is about 10% faster than mine.

Have fun,
Bernd