Split a word into Two cells

[Vikas Kumar]

Hi Friends,

I am trying to achieve something like this using VBA:
I have some sentences in first columns as mentioned below and would like to split them into just two cells.


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]Text
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col width="71"></colgroup><tbody>[TR]
[TD="class: xl65, width: 71"]First Word
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]Remaining text
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]Vikas Kumar[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Vikas
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]Kumar
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]Harry Potter
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Harry [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]Potter[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]Gautam Buddha Nagar
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Gautam [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]Buddha Nagar[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]This is a Machine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]This
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]is a Machine
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 170"]
<colgroup><col width="170"></colgroup><tbody>[TR]
[TD="class: xl65, width: 170"]Where are you going Now
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Where [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<colgroup><col width="124"></colgroup><tbody>[TR]
[TD="class: xl65, width: 124"]are you going Now
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Here I am able to find out column "First Word" & 'Remaining Text" with the help of formula, But unable to get this done by VBA.

Can anyone please help upon this.

 

[footoo]

.

 

[wideboydixon]

Public Sub SplitText()

Dim lastRow As Long
Dim thisRow As Long
Dim spacePos As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
    spacePos = InStr(1, Cells(thisRow, 1).Value & " ", " ")
    Cells(thisRow, 2).Value = Left(Cells(thisRow, 1).Value, spacePos - 1)
    Cells(thisRow, 3).Value = Mid(Cells(thisRow, 1).Value, spacePos + 1)
Next thisRow

End Sub

WBD

 

[Vikas Kumar]

Public Sub SplitText()

Dim lastRow As Long
Dim thisRow As Long
Dim spacePos As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
    spacePos = InStr(1, Cells(thisRow, 1).Value & " ", " ")
    Cells(thisRow, 2).Value = Left(Cells(thisRow, 1).Value, spacePos - 1)
    Cells(thisRow, 3).Value = Mid(Cells(thisRow, 1).Value, spacePos + 1)
Next thisRow

End Sub

WBD

Wow wideboydixon!!!!! This did the task.

Thanks for quick reply and the solution.