Parsing excel string of numbers using vba

Question

I am trying parse a number string and create rows accordingly. On the left of the Example Data picture is an example of the input data with the right being my desired output. I am wanting to insert a unique row of data for each digit within the brackets for each number combination.

Example Data

Here is an example of the code I used to try to solve the problem.

Option Explicit

Sub example()


Dim num As Variant
Dim x As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim test As Variant
Dim test2 As Variant
Dim count As Integer

m = 0

For i = 1 To 3

num = Range("C" & 5 + i + m).Value

For j = 1 To Len(num)

    test = Mid(num, j)

    If Left(Mid(num, j), 1) = "[" Then

        For k = 1 To Len(num) - (j + 1)

            m = m + 1

            Range("C" & 5 + m + i - 1).EntireRow.Insert

            test2 = Left(Mid(num, j + k), 1)

            Range("C" & 5 + m + i - 1).Value = Left(num, j - 1) + test2

        Next k

    End If

Next j

Next i

End Sub

Also, for the purpose of my example I have the output data starting in column G. For my data I actually want to insert the output in place of where the existing input data was while inserting rows accordingly. This is because within my input data there will be number combinations that do not need to be manipulated interspersed within my data. — Rookie
– Rookie, Commented Jun 17, 2016 at 19:54

nbayly · Accepted Answer · 2016-06-17 22:11:20Z

1

Please consider using the following script:

Sub splitcombinations()

Dim rngCell As Range
Set rngCell = ThisWorkbook.Sheets(1).Range("A2")

Dim strCombinationDigits As String, strBaseDigits As String
Dim intCombinationDigitsLen As Integer

Dim x As Integer

Do While rngCell.Value2 <> ""

    If InStr(rngCell.Value2, "[") > 0 Then

        strCombinationDigits = Mid(rngCell.Value2, InStr(rngCell.Value2, "[") + 1, InStr(rngCell.Value2, "]") - InStr(rngCell.Value2, "[") - 1)
        intCombinationDigitsLen = Len(strCombinationDigits)

        strBaseDigits = Left(rngCell.Value2, InStr(rngCell.Value2, "[") - 1)

        ActiveSheet.Range(rngCell.Offset(1, 0), rngCell.Offset(intCombinationDigitsLen - 1, 0)).EntireRow.Insert

        For x = 1 To intCombinationDigitsLen

            rngCell.Offset(x - 1, 0).Value2 = strBaseDigits & Mid(strCombinationDigits, x, 1)
            rngCell.Offset(x - 1, 1).Value2 = rngCell.Offset(0, 1).Value2
            rngCell.Offset(x - 1, 2).Value2 = rngCell.Offset(0, 2).Value2

        Next

    End If

    Set rngCell = rngCell.Offset(intCombinationDigitsLen , 0)

Loop

End Sub

edited Jun 17, 2016 at 22:11

answered Jun 17, 2016 at 21:40

nbayly

2,1672 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nbayly Over a year ago

Actually what you are suggesting assumes a set number of characters within the brackets (7). I actually calculate how many digits with intCombinationDigitsLen and modify how many rows I insert accordingly. Then the reason why I only offset by 1 row each loop is because then I'm checking on every iteration if rngCell contains an opening bracket, assuming if it doesn't (and not blank) that it's an iteration that the script added. It then moves down again. I guess I could offset by intCombinationDigitsLen but wanted to make sure I wasn't missing anything.

Brian Over a year ago

Nice code! Just change Set rngCell = rngCell.Offset(1, 0) to Set rngCell = rngCell.Offset(intCombinationDigitsLen, 0)

Brian Over a year ago

I see. Okay, got it!

nbayly Over a year ago

Ahh, I was just responding to your previous post. Yes, that would remove a bunch of needless iterations. I will edit accordingly.

Rookie Over a year ago

I have tried your script and it looks to be working well.

Collectives™ on Stack Overflow

Parsing excel string of numbers using vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related