CODEWITHSUNDEEP
vbaexcel
Sean
Sean

Reputation: 79

Substring with Excel VBA

I have been using this code as a starting point: https://danwagner.co/how-to-transpose-horizontal-data-to-vertical-data-for-easy-pivot-tables/

One one of my cells Ax (x referring to the number), the content is ABCDEFGHI and I want to substring the cells every 2 characters, and the last set is 3 characters. Final result looks like:

AB CD EF GHI

At line 44, using the variable 

varDetails = .Range(.Cells(lngIdx, 1), .Cells(lngIdx, 4))

and think that is where I need to modify the code. I am not fluent enough with VBA and need some help.

Upvotes: 0

Views: 157

Answers (1)

Teasel
Teasel

Reputation: 1340

To split the data from your string you can use the following code

Sub SplitStringEveryTwoCharacters()
    Dim arrayWithValuesByTwo() As String
    Dim myString As String

    'Just replace with your data
    myString = "ABCDEFGHIJKLM"

    'Resize
    ReDim arrayWithValuesByTwo(Len(myString) - 1)

    'For each 2 character in string
    For i = 1 To Len(myString) Step 2
        'Add in array
        If (i <= Len(myString) - 1) Then
            arrayWithValuesByTwo(i - 1) = Mid$(myString, i, 2)
        End If

        If (i = Len(myString)) Then
            arrayWithValuesByTwo(i - 1) = Mid$(myString, i, 1)
        End If
    Next
End Sub

What you need to change

Here I have set my string into a variable with myString = "ABCDEFGHIJKLM" but you can easily change this and take it directly from a cell with something like myString = Range("A5").

You can access you data with arrayWithValuesByTwo(1) for example. Just loop through it to get all of the values.

Upvotes: 1

Related Questions