Split a string

[Conrad]

I have a string like, let's say, "aabbccddeeffgghh" in one cell and I want to convert it in something like "aa" "bb" "cc" "dd" "ee" "ff" "gg" "hh", with each group of 2 characters in a separate cell. I think I know how to put each group in separate cells (using Data/Text to columns), but I don't know how to convert from "aabbccddeeffgghh" to "aa bb cc dd ee ff gg hh" (how to add the space after each 2 chars). How can I do it, using a macro (so automatically, not manual :) ).

Thanks in advance!

[Bob Phillips]

For i = 1 To Len(Range("A1").Value) Ste p 2

tmp = tmp & Mid$(Range("A1").Value, i, 2) & " "
Next i
Range("B1").Value = tmp

[GTO]

Howdy All,

I read this a bit differently, but pretty beat up, so may well be off...


Sub ex()
Dim i As Long, s As String

For i = 1 To Int(Len(Range("A1")) / 2)
s = s & "[xx]"
Next

Range("A1").Parse s
End Sub


...or if you knew the initial string would never be longer than x...

Sub ex2()
Range("A1").Parse Left("[xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx]" & _
"[xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx][xx]" & _
"[xx][xx]", Len(Range("A1").Value) * 2)
End Sub


Again, I may well be off...

Mark

Edit: ACK! I had an Int() in the second one, which was my bad...

[p45cal]

Adapting a recorded macro you might use this to split the selected block of cells:Selection.TextToColumns Destination:=Selection.Offset(, 1), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(2, 1), Array(4, 1), Array(6, 1), Array(8, 1), _
Array(10, 1), Array(12, 1)), TrailingMinusNumbers:=Truebut this would only ever handle up to 7 pairs of letters.
However you can just manually add to it, or you can let the code do it for you; just alter the value of j to the max number of pairs of letters:Sub Macro1()
Dim yy()
j = 15
ReDim yy(0 To j)
For i = 0 To j
yy(i) = Array(i * 2, 1)
Next i
Selection.TextToColumns Destination:=Selection.Offset(, 1), _
DataType:=xlFixedWidth, FieldInfo:=yy, _
TrailingMinusNumbers:=True
End SubYou could even let the code determine j for you.
(You can remove the .Offset(, 1) if you don't want to preserve your original column of info.)