Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel data breakout
I receive this file that has mulitple order numbers populated in one row. I
need each order number to be on a seperate row. Is there a formula that I can do to make this happen? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel data breakout
The Transpose function will do that. Look in help.
-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Kelly" wrote in message I receive this file that has mulitple order numbers populated in one row. I need each order number to be on a seperate row. Is there a formula that I can do to make this happen? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get data from a web page to chart in excel. Any solutions? | Excel Discussion (Misc queries) | |||
Excel: How to choose data on two separate rows in the same column | Excel Worksheet Functions | |||
Getting Excel Data from One Sheet to Another.... | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |