copy and paste split

**stevesunfold** · 07-20-2008, 09:24 AM

hi guys
if i copy and paste this into excel

Redcar 14.20 Mohanad
Newt 14.30 Archduke Ferdinand
Strat 14.40 Santera
Redcar 14.50 Brouhaha
Newt 1500 Foolish Myth

how can i get it so the course is in one column,time in another and horse in another?

thanks in advance

**stevesunfold** · 07-20-2008, 12:33 PM

anyone please?

**olasa** · 07-20-2008, 01:02 PM

In the menu: under Data select Text to Columns...
//Ola

**VBA Noob** · 07-20-2008, 02:29 PM

For the example given try

Dim Rng As Range
Set Rng = Selection
    
    With Rng
        .Replace What:="0 ", Replacement:="0z"
        .Replace What:=".", Replacement:=":"
        .TextToColumns Destination:=Cells(Rng.Row, Rng.Column), DataType:=xlDelimited, _
        Space:=False, Other:=False
    End With
    
    With Rng
         .TextToColumns Destination:=Cells(Rng.Row, Rng.Column), DataType:=xlDelimited, _
         Other:=True, OtherChar:="z", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    End With
        
    With Columns(Rng.Column + 1)
        .Insert Shift:=xlToRight
        .NumberFormat = "HH:MM"
    End With
   
    With Rng
         .TextToColumns Destination:=Cells(Rng.Row, Rng.Column), DataType:=xlDelimited, _
         Space:=True
    End With
    
    Set Rng = Nothing

VBA Noob