Copy/Text To Columns/Transpose in VBA
Land the cell pointer on a cell with the string of numbers, and run
this code. It writes everything between two spaces to a new cell
underneath the original cell.
Sub Parse()
Dim CellVal As String
Dim LastSpace As Long
Dim Entry As Variant
Dim NextRow As Long
Dim K As Long 'counter
CellVal = Trim(ActiveCell.Value) & " "
For K = (LastSpace + 1) To Len(CellVal)
If Mid(CellVal, K, 1) < " " Then
Entry = Entry & Mid(CellVal, K, 1)
Else
LastSpace = K 'reset LastSpace column number
NextRow = NextRow + 1 'increment next row
ActiveCell.Offset(NextRow, 0).Value = Entry 'write to the next row
Entry = "" 'reset
End If
Next K
End Sub
|