Avatar of Philippe Renaud
Philippe Renaud
Flag for Canada asked on

Help manipulating a String

Hello EE, I have this code:

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        replacementStep = 5

        Dim input As String = "=C3-C4"
        Dim result As String = System.Text.RegularExpressions.Regex.Replace(input, "([A-Z]+)(\d+)", AddressOf ReplacementDelegate)

    End Sub

Open in new window

    Private Function ReplacementDelegate(ByVal m As System.Text.RegularExpressions.Match) As String
        Const Letter_Z As Char = "Z"c
        Const Letter_A As Char = "A"c

        Dim convertedLetters() As Char = m.Groups(1).Value.ToCharArray()

        convertedLetters(convertedLetters.Length - 1) = Chr(Asc(convertedLetters(convertedLetters.Length - 1)) + replacementStep)

        ' Adjust for Zs
        For i As Integer = convertedLetters.Length - 1 To 0 Step -1
            If convertedLetters(i) > Letter_Z Then
                convertedLetters(i) = Chr((Asc(Letter_A) - 1) + (Asc(convertedLetters(i)) Mod Asc(Letter_Z)))

                If i = 0 Then
                    Dim temp(convertedLetters.Length) As Char

                    Array.Copy(convertedLetters, 0, temp, 1, convertedLetters.Length)
                    convertedLetters = temp
                    convertedLetters(0) = Letter_A

                    Exit For
                    convertedLetters(i - 1) = Chr(Asc(convertedLetters(i - 1)) + 1)
                End If
                Exit For
            End If

        Dim convertedNumber As Integer = Convert.ToInt32(m.Groups(2).Value)

        convertedNumber += 1

        Return (New String(convertedLetters) + convertedNumber.ToString())
    End Function

Open in new window

you will see that by changing the value replacementStep it will change the letter in the output .. from  =C3-C4 to lets say =H4-H5   etc..

if you do replacementStep to 49   it will be:  =AZ4-AZ5   because after 26 its like in excel you increase a letter...  but after 49 I should get =BA4-BA5 or more  but instead im having =A]4-A]5   this is wrong

this code comes from Kaufmed. Its a new question for him but if anyone wnats to try to help...

.NET ProgrammingVisual Basic.NETC#

Avatar of undefined
Last Comment
Philippe Renaud

8/22/2022 - Mon

Sorry for the delay. I cannot check my personal email while at work, so I didn't see your previous posts until I took lunch  = )

As to your issue, is "=C3-C4" the value you are testing step=49 with? I get this:


Never mind. I misinterpreted the question. Be back in a minute  = )
Philippe Renaud

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Philippe Renaud

Amazing Kaufmed. thank you a lot.
no worries for the time!

Glad to help  = )
Philippe Renaud

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Philippe Renaud

Kaufmed, I think I found a little bug...

lets say string is =AA15 + AA17

if replcementStep = 0, result will be =BA16 + BA18

but at 0 it should not increase letter... (like it does well if string is:  =A1+A2  result will be:  =A2+A3)

and also... it should increment to AB and not BA

want me to create a new question for that?

Nah. Just ad the following at line 5:

If replacementStep <= 0 Then
    Return m.Value
End If

Open in new window

Philippe Renaud

Kaufmed, new question regarding this one... 500 points...

ive found 2 problems... I hope you can help soon...  : /


thanks a lot
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.