Trying to split a single cell with multiple variables

Question

I have non-microsoft files that have look along the lines of:

>gibberish that changes AAARRGGGHHHH

Now, I have a code to make a new .xlsx file out of this to split using Trying to convert files while keeping the old name.

However, I would like the "A2" cell contents to split with each indivual letter being assigned a cell and then have the former contents deleted. I don't mind if this ends up in A3 till AZ.

Thus, the above example I would like to transform to make it look like:

>gibberish that changes
A A A R R G G G H H H H

To clarify "Gibberish that changes" is not a constant it changes per file I have what is denoted here. Same holds true for the second line.

Based on Split cell string into individual cells

I tried this code:

Dim sVar1 as string
Dim sVar2 as string

I = InStr(1, strX, "A" & "R" & "G" & "H")

sVar1 = mid(strX, 1, I)
sVar2 = mid(strx,i+1)

However, this yields no results. It does not cause the Macro to fail (as I get no error message and the rest of the macro works (changing a file into another format and altering the name), but it doesn't do anything. I would like to use the string as the files constantly change in contents and order in cell A2.

I also have no true delimiter as things like ARRGHHHH is written as one word, is that causing the issue?

`have no true delimiter as things like ARRGHHHH is written as one word, is that causing the issue?` yes, yes it is XD — findwindow, Jun 02 '16 at 16:03

score 4 · Answer 1 · answered Jun 02 '16 at 17:24

4

my 0.02 with Character object

Sub main()
    With Range("A2")
        For i = 1 To Len(.Value)
            .Offset(, i) = .Characters(i, 1).Text
        Next i
    End With
End Sub

answered Jun 02 '16 at 17:24

user3598756

28,893
4
18
28

Like @Gary's Student this does nearly what I want, but not entirely. It does get me further, thank you. – GMV Jun 03 '16 at 10:45
I see. But what do you really want? – user3598756 Jun 03 '16 at 10:53

Gary's Student · Accepted Answer · 2016-06-03T14:03:07.890

1

This will parse A2 into its characters and place the characters next to A2, each in its own cell:

Sub dural()
With Range("A2")
    v = .Value
    L = Len(v)
    For i = 1 To L
        .Offset(0, i).Value = Mid(v, i, 1)
    Next i
End With
End Sub

EDIT#1:

This will handle both a range of input cells and the clearing of the original input data. Before:

The new macro:

Sub dural2()
    Dim rng As Range, r As Range, v As Variant
    Dim L As Long, i As Long
    Set rng = Range("A2:A40")
        For Each r In rng
        v = r.Value
        L = Len(v)
        For i = 1 To L
            r.Offset(0, i - 1).Value = Mid(v, i, 1)
        Next i
    Next r
End Sub

The result:

edited Jun 03 '16 at 14:03

answered Jun 02 '16 at 16:23

Gary's Student

95,722
10
59
99

That looks nearly like I want it to be, but I would like the A2 to be gone. It is all to be meant to create a new file (for which I have a working macro). – GMV Jun 03 '16 at 10:09
1

Managed by adding a new macro and calling them up in a new one. Thank you. – GMV Jun 03 '16 at 11:41
I am glad you found a solution! – Gary's Student Jun 03 '16 at 11:42
I ran into a little snag. I found out that some files have more lines with information, I thought it was only stored on A2... So I tried to alter your code, with Range("A2:A40"), but that fails and if I alter the value of v to be as long as the longest line (which is A2), it repeats the A2 line also in the 3rd row and beyond... I can make it work by repeating this code for each line, but can this be done more elegantly? – GMV Jun 03 '16 at 13:03
First clarify the **A2** issue...........do you want **A2** to end up *empty* or do you want **A2** to end up containing the *first character* and the adjacent cells containing the other characters. – Gary's Student Jun 03 '16 at 13:42
I don't mind either way, but preferably to have A2 containing the first character. More importantly, The characters of A3, A4 etc. Continuing behind the cells of A2. – GMV Jun 03 '16 at 13:48
@GMV Now I understand. *(maybe)* – Gary's Student Jun 03 '16 at 13:49
@GMV See my **EDIT#1:** – Gary's Student Jun 03 '16 at 14:03
That indeed did the trick. I am sorry that apparently I wasn't clear enough about what I wanted to accomplish. – GMV Jun 03 '16 at 15:26
@GMV We persevere until something useful is generated. – Gary's Student Jun 03 '16 at 15:38

score 0 · Answer 3 · answered Jun 02 '16 at 16:17

0

Would this be helpful at all?

Sub Test()

Dim i As Integer
Dim num As Integer

num = Len(Range("A1"))

For i = 1 To num
    Debug.Print Mid(Range("A1"), i, 1)
Next

End Sub

answered Jun 02 '16 at 16:17

Brian

2,078
1
15
28

No errors given when I parse the macro, but it does not do anything so it seems. Even if I alter the Range towards A2. – GMV Jun 03 '16 at 10:47
@GMV Sorry, it is a generic code that prints to the immediate window. It is only to demonstrate how to split the non-delimited word in `Range("A1")`. Let me know what to do with it and I can adjust the code. The immediate window is in the Visual Basic Editor. Just go there and press `ctrl` + `G` – Brian Jun 03 '16 at 10:58

J. felix · Answer 4 · 2016-06-03T12:14:42.953

0

Try this.

    Sub dural()
    With Range("A2")
        v = .Value
        L = Len(v)
        For i = 0 To L - 1
            If i = 0 Then
                .Offset(0, i).Value = Left(v, 1)

            Else
                .Offset(0, i).Value = Mid(v, i, 1)
            End If
        Next i
    End With
    End Sub

Input

output

edited Jun 03 '16 at 12:14

answered Jun 02 '16 at 16:50

J. felix

31
1
9

Trying to split a single cell with multiple variables

4 Answers4