Substring with Excel VBA

Question

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.

Mid() extracts substrings. If you have a problem in using Mid(), please explain what the problem is. The code snippet that you give lacks context (saying that it is at line 44 doesn't help us) and appears to have little to do with the rest of your question. — John Coleman
– John Coleman, Commented Oct 12, 2017 at 12:10
I don't know how to incorporate that with the range in the above variable with the code I am using. — Sean
– Sean, Commented Oct 12, 2017 at 12:15
If varDetails contains this string with 9 characters, then you can us Mid() to extract the substrings that you want. It is an easy function to use: homeandlearn.org/excel_vba_mid_function.html — John Coleman
– John Coleman, Commented Oct 12, 2017 at 12:23
I am using the code (see link in original post) trying to de-pivot data with the same number of cells as the code sample. Instead of using cells A2-A4, I would rather take cell A1 ABCDEDGHI and substring it across A2-A4 — Sean
– Sean, Commented Oct 12, 2017 at 12:29
If you don't want lngIdx to start at 2 but would rather have it start at 5, replace the 2 in For lngIdx = 2 To lngLastRow by 5. That seems to address the issue in your comment. Not meaning to sound harsh, but if you want to use VBA then you should learn VBA. Making random changes to code that you find on the internet but don't understand isn't a reliable way to get code which does what you want. — John Coleman
– John Coleman, Commented Oct 12, 2017 at 12:45

Teasel · Accepted Answer · 2017-10-12 13:46:17Z

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.

Collectives™ on Stack Overflow

Substring with Excel VBA

1 Answer 1

What you need to change

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

What you need to change

Comments

Your Answer

Sign up or log in

Post as a guest

Related