split data in cells VBA

[davewatson86]

hi all

this is probably really simple but all i have been ale to find is spit with commas etc.

i have data that displays as below in column A


[TABLE="width: 286"]
<colgroup><col></colgroup><tbody>[TR]
[TD]102269 Robinson's Body Centre [/TD]
[/TR]
[TR]
[TD]102288 C & S Bodycare Ltd [/TD]
[/TR]
[TR]
[TD]102329 Cooper Norwich [/TD]
[/TR]
[TR]
[TD]102362 V B S [/TD]
[/TR]
[TR]
[TD]102430 Bracken Brae Garage [/TD]
[/TR]
[TR]
[TD]102452 B.M Page & Son [/TD]
[/TR]
[TR]
[TD]102455 Frettenham Service Station [/TD]
[/TR]
[TR]
[TD]102477 H Curtis & Son [/TD]
[/TR]
[TR]
[TD]102526 Leeders Accident Centre Ltd [/TD]
[/TR]
[TR]
[TD]102531 Langor Bridge Garage [/TD]
[/TR]
[TR]
[TD]102542 R & M Eke Motor Engineers [/TD]
[/TR]
[TR]
[TD]102654 Banham Vehicle Services Ltd [/TD]
[/TR]
[TR]
[TD]102675 Scole Engineering [/TD]
[/TR]
[TR]
[TD]102691 Cathedral Garage [/TD]
[/TR]
[TR]
[TD]102701 Surlingham Garage [/TD]
[/TR]
[TR]
[TD]102703 Yelverton Garage Ltd


what would be the most efficient way to separate this data into this.

column A Column B
[TABLE="width: 299"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]102269[/TD]
[TD]Robinson's Body Centre [/TD]
[/TR]
[TR]
[TD]102288[/TD]
[TD]C & S Bodycare Ltd [/TD]
[/TR]
[TR]
[TD]102329[/TD]
[TD]Cooper Norwich [/TD]
[/TR]
[TR]
[TD]102362[/TD]
[TD]V B S [/TD]
[/TR]
[TR]
[TD]102430[/TD]
[TD]Bracken Brae Garage [/TD]
[/TR]
[TR]
[TD]102452[/TD]
[TD]B.M Page & Son [/TD]
[/TR]
[TR]
[TD]102455[/TD]
[TD]Frettenham Service Station [/TD]
[/TR]
[TR]
[TD]102477[/TD]
[TD]H Curtis & Son [/TD]
[/TR]
[TR]
[TD]102526[/TD]
[TD]Leeders Accident Centre Ltd [/TD]
[/TR]
[TR]
[TD]102531[/TD]
[TD]Langor Bridge Garage [/TD]
[/TR]
[TR]
[TD]102542[/TD]
[TD]R & M Eke Motor Engineers [/TD]
[/TR]
[TR]
[TD]102654[/TD]
[TD]Banham Vehicle Services Ltd [/TD]
[/TR]
[TR]
[TD]102675[/TD]
[TD]Scole Engineering [/TD]
[/TR]
[TR]
[TD]102691[/TD]
[TD]Cathedral Garage [/TD]
[/TR]
[TR]
[TD]102701[/TD]
[TD]Surlingham Garage [/TD]
[/TR]
[TR]
[TD]102703[/TD]
[TD]Yelverton Garage Ltd [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
i have thousands of Rows of data to process every time the data is updated.

thank you for any help.

Dave[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

You could use these two:

=0+LEFT(A2,FIND(" ",A2)-1)

=MID(A2,FIND(" ",A2)+1,999)

 

[Peter_SSs]

You could just as well do this manually with Text to Columns (on the Data ribbon tab) but if you want to do it by vba per your thread title, try the following in a copy of your workbook.

Sub Text_To_Columns()  
  Range("A1", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1))
End Sub

 

[davewatson86]

You could just as well do this manually with Text to Columns (on the Data ribbon tab) but if you want to do it by vba per your thread title, try the following in a copy of your workbook.

Sub Text_To_Columns()  
  Range("A1", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1))
End Sub

thank you Peter_SSs

that worked a treat

 

[Peter_SSs]

thank you Peter_SSs

that worked a treat

You're welcome. Thanks for letting us know.