Text to Columns
Use the TextToColumns method of the Range object.
expression.TextToColumns(Destination, DataType,TextQualifier, ConsecutiveDelimiter, Tab, _ Semicolon, Comma, Space, Other,OtherChar, FieldInfo, DecimalSeparator, _ ThousandsSeparator,TrailingMinusNumbers)
The code below relates to the data shown above. To perform a simple text to columns procedure you only need to use the Destination & DataType parameters and then specify the delimiter eg Space:= True. DataType defaults to delimited rather than fixed width, so strictly not needed in the code below.
Range("B1")= "First Name" Range("C1")= "Last Name" Range("A2",Range("A2").End(xlDown)).TextToColumns _ Destination:=Range("B2"), DataType:=xlDelimited,Space:=True Columns("B:C").AutoFit Range("A1",Range("A1").End(xlDown)).Copy Range("B1",Range("B1").End(xlToRight).End(xlDown)).PasteSpecial _ Paste:=xlPasteFormats Application.CutCopyMode= False