Assuming the original strings are in column A staring at row 2, try this.
VBA Code:
Sub FourColumns()
With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(ISNUMBER(LEFT(A2,1)+0),"" "","""") & A2"
.Value = .Value
.TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
End With
End Sub
Assuming the original strings are in column A staring at row 2, try this.
VBA Code:
Sub FourColumns()
With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(ISNUMBER(LEFT(A2,1)+0),"" "","""") & A2"
.Value = .Value
.TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
End With
End Sub
Sub TwoColumns()
With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("substitute(" & .Offset(, -1).Address & ","" "","";"",1)")
.TextToColumns DataType:=xlDelimited, Semicolon:=True, Space:=False, Other:=False
End With
End Sub
BTW, suggest that you update your Account details to show us version information as it may affect the most appropriate suggestion.
.. and also give us sample data in a better format? .. XL2BB