Hi,

Am Tue, 10 Dec 2013 08:59:50 -0800 (PST) schrieb :

Thanks for this unfortunately it splits column A over 2 columns but but not original columns B and C. I showed 3 columns of data but sometimes there are up to 20 columns that need to be split if this makes a difference.

if TextToColumns doesn't work try:

Sub SplitString()
Dim LRow As Long
Dim LCol As Long
Dim i As Long
Dim Start As Integer
Dim rngC As Range

With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To 2 * LCol Step 2
.Columns(i).Insert
LRow = .Cells(Rows.Count, i - 1).End(xlUp).Row
For Each rngC In .Range(.Cells(1, i - 1), .Cells(LRow, i - 1))
Start = InStrRev(rngC, " ")
rngC.Offset(, 1) = Trim(Mid(rngC, Start + 1, 99))
rngC = Trim(Left(rngC, Start - 1))
Next
Next
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2