View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default 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