View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default Excel data breakout

Hi, Kelly-
Are the multiple orders on one row contained in one order number per
cell, or all in one cell?

If it's one order per cell, in many cells on that row, you can copy the
cells, then click ~Edit ~Paste Special, click the Transpose box, and
click OK.

If it's many order numbers within a single cell, you can paste the
following code into your spreadsheet as a macro. Land the cell pointer
on the cell with order numbers, and run this code: the individual order
numbers will appear immediately below it.

Sub Parse_Data_Between_Spaces()
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