Help with separating data in VBA

[williamsabbie]

Hi Guys & Girls,

I know there are a number of ways of splitting names by space, but i want to ignore the 2nd space.

So if the name is John Fred Bloggs.
I want it to be
[TABLE="width: 50"]
<tbody>[TR]
[TD]John
[/TD]
[TD]Fred Bloggs
[/TD]
[/TR]
</tbody>[/TABLE]


I have 2 macros built- one is a text to columns and the other is below. But both split into 3 or 4, rather than just 2 like i want.

Sub Split_Space()


Dim cell As Range
Dim cell1 As Range
Dim i As Long
Dim J As Long
Dim K As Long
Dim lngFindCol As Long
Dim lngFindCol1 As Long
Dim VarSplitString As Variant
Dim VarSplitString1 As Variant
Dim strContainer As String
Dim strContainer1 As String

i = 1
For Each cell In Range("c1:c" & Sheet1.Range("c" & Rows.Count).End(xlUp).Row)
VarSplitString = Split(cell.Value, " ")
For J = 1 To UBound(VarSplitString)
Sheet1.Cells(i, J + 4) = VarSplitString(J)
Next
i = i + 1
Next cell
i = 1
For Each cell In Range("c1:c" & Sheet1.Range("c" & Rows.Count).End(xlUp).Row)
VarSplitString = Split(cell.Value, " ")
For J = 1 To UBound(VarSplitString)
Sheet1.Cells(i, J + 3) = VarSplitString(K)
Next
i = i + 1
Next cell


End Sub

 

[Joe4]

Welcome to the Board!

I know there are a number of ways of splitting names by space, but i want to ignore the 2nd space.

Try this:

Sub Split_Space()

Dim cell As Range
Dim VarSplitString As Variant

For Each cell In Range("c1:c" & Sheet1.Range("c" & Rows.Count).End(xlUp).Row)
    VarSplitString = InStr(cell, " ")
    cell.Offset(0, 1) = Left(cell, VarSplitString - 1)
    cell.Offset(0, 2) = Mid(cell, VarSplitString + 1, Len(cell))
Next cell

End Sub

 

[williamsabbie]

Thank you Joe,

That code worked fine, but if it came to a cell that only had a first name, it stopped.
My colleague helped me, and we came up with:

Sub Split_Space3()


Dim cell As Range
Dim i As Long
Dim J As Long
Dim K As Long
Dim VarSplitString As Variant
Dim strContainer As String
Dim strContainer1 As String

K = 1
For Each cell In Range("c1:c" & Sheet1.Range("c" & Rows.Count).End(xlUp).Row)
VarSplitString = Split(cell.Value, " ")
J = UBound(VarSplitString)
strContainer = VarSplitString(0)
If J > 0 Then strContainer1 = VarSplitString(1)
If J > 1 Then
For i = 2 To J
strContainer1 = strContainer1 & " " & VarSplitString(i)
Next i
End If

Sheet1.Range("D" & K) = strContainer
Sheet1.Range("E" & K) = strContainer1
K = K + 1
Next cell


End Sub

 

[Joe4]

That code worked fine, but if it came to a cell that only had a first name, it stopped.

You didn't mention that was a possibility, or else I would have programmed for that.

But glad you guys were able to figure it out.

 

[williamsabbie]

You didn't mention that was a possibility, or else I would have programmed for that.

But glad you guys were able to figure it out.

I didn't realise till I ran the code & went to the bottom of the data set and saw the blanks. It was an 'Oh, it also contains unknown. Doh'

 

[Joe4]

No worries. Sounds like it gave you guys a chance to break down the code a little bit and figure out how to alter it. That's not a bad thing!