Link to home
Start Free TrialLog in
Avatar of Philippe Renaud
Philippe RenaudFlag 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...

Avatar of kaufmed
Flag of United States of America image

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:

User generated image
Never mind. I misinterpreted the question. Be back in a minute  = )
Avatar of Philippe Renaud


Avatar of kaufmed
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Amazing Kaufmed. thank you a lot.
no worries for the time!
Glad to help  = )
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

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

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

thanks a lot