Increment each of the character in a string by 1 [VBA]

[x0nar]

I'd like to increment each of the character within a string by 1

So for eg. ABCD101 becomes BCDE212

The nearest I've got is using char combined by code function
ie.
=CHAR(CODE("cellreference")+1).

I would really appreciate if the function would be a User Defined Function [UDF] in VBA.

Thanks for your help

 

[Misca]

Here's my suggestion:

Function NewText(MyText As String) As String

Dim i As Integer
Dim Characters As String
Dim Ch As String
Dim NextCh As Integer


Characters = "ABCDEFGHIJKLMNOPQRSTUVWXZÅÄÖA01234567890"
Characters = Characters & LCase(Characters)


For i = 1 To Len(MyText)
    Ch = Mid(MyText, i, 1)
    NextCh = InStr(Characters, Ch) + 1
    
    If NextCh > 1 Then 'Found the character in Characters string
        Ch = Mid(Characters, NextCh, 1)
    End If
    NewText = NewText & Ch
Next i


End Function

 

[x0nar]

Here's my suggestion:

Function NewText(MyText As String) As String

Dim i As Integer
Dim Characters As String
Dim Ch As String
Dim NextCh As Integer


Characters = "ABCDEFGHIJKLMNOPQRSTUVWXZÅÄÖA01234567890"
Characters = Characters & LCase(Characters)


For i = 1 To Len(MyText)
    Ch = Mid(MyText, i, 1)
    NextCh = InStr(Characters, Ch) + 1
    
    If NextCh > 1 Then 'Found the character in Characters string
        Ch = Mid(Characters, NextCh, 1)
    End If
    NewText = NewText & Ch
Next i


End Function

Thanks. That worked.
From what I gather you have defined an array of characters. And then you search that within the that array and ask it to shift one step further. So if I want to include symbols such as @ or _ or . would it work if I input them in defined array of yours ?
Thanks again.

 

[Fluff]

This is another option

Function NC(MyStr As String) As String

    Dim cnt As Long
    Dim Ch As String

    For cnt = 1 To Len(MyStr)
        Ch = Ch & Chr(Asc(Mid(MyStr, cnt, 1)) + 1)
    Next cnt
    NC = Ch

End Function

It will increase the ascii value of each character by 1

 

[Misca]

Go ahead and try. It will work. I left them out because I don't know what is the desired / correct order of those special characters. And there are even some foreign alphabets missing from my list of characters for the same reason.

 

[x0nar]

Edit: Wrong Quote

 

[x0nar]

This is another option

Function NC(MyStr As String) As String

    Dim cnt As Long
    Dim Ch As String

    For cnt = 1 To Len(MyStr)
        Ch = Ch & Chr(Asc(Mid(MyStr, cnt, 1)) + 1)
    Next cnt
    NC = Ch

End Function

It will increase the ascii value of each character by 1

Thanks a lot. Since the code is based on ASCII shifting, I'm guessing it will work on all types of characters including symbols . Thanks again.

 

[x0nar]

Go ahead and try. It will work. I left them out because I don't know what is the desired / correct order of those special characters. And there are even some foreign alphabets missing from my list of characters for the same reason.

Yep. Tried it and appears to have worked. Thanks again.