Split Row Data In To Multiple Columns Based On Delimiter

Hi Rick

http://www.excelfox.com/forum/f22/re...ther-data-420/ is great - I need to do exactly this except with multiple columns

for example looks like:

Meals, Accom, Labour 100,200,300 200,400,600 500,700,100

needs to be:

Meals 100 200 500
Accom 200 400 700
Labour 300 600 100


In my actual spreadsheet more than 3 columns need to be split, columns a-->s are single line and then columns T--> AF all need to be split

Any help you can provide would be greatly appreciated - I am pretty good in Excel but not at all good with VBA.

Thank you in advance.
C

Quote:

---

Originally Posted by Rick Rothstein

The following scenario seems to come up somewhat often at the various forums I participate in... take a table of data where one column contains delimited data and split that delimited data so that each item is on a row of its own, copying the associated data into the blank cells created by the split. Visually, we want to go from this table...

	A	B	C	D
1	Name	Client Number	Parts Ordered
2	Rick	1111111	P1, P2, P3
3	Sam	2222222	P2, P5
4	Joe	3333333	P3
5	Bill	4444444	P4, P6, P7, P8
6

to this one

	A	B	C	D
1	Name	Client Number	Parts Ordered
2	Rick	1111111	P1
3	Rick	1111111	P2
4	Rick	1111111	P3
5	Sam	2222222	P2
6	Sam	2222222	P5
7	Joe	3333333	P3
8	Bill	4444444	P4
9	Bill	4444444	P6
10	Bill	4444444	P7
11	Bill	4444444	P8
12

Below is a macro that will accomplish this task. Note though that I have generalize it somewhat. Usually in the requests the delimited data is in the last column as shown above, however, there is no need for this to be the case... this macro will allow any column to be the delimited column.

Code:

---

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
  Const Delimiter As String = ", "
  Const DelimitedColumn As String = "C"
  Const TableColumns As String = "A:C"
  Const StartRow As Long = 2
  Application.ScreenUpdating = False
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) Then
      Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
    End If
    Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error GoTo NoBlanks
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  On Error GoTo 0
  For Each A In Table.SpecialCells(xlBlanks).Areas
    A.FormulaR1C1 = "=R[-1]C"
    A.Value = A.Value
  Next
NoBlanks:
  Application.ScreenUpdating = True
End Sub

---

There are four constants (the Const statements) that you need to match to your actual worksheet conditions in order to work. The first is named Delimiter and it can be one or more characters long. The second is named DelimitedColumn and specifies the column letter containing the delimited cells. The third is named TableColumns and it specifies the columns occupied by your data (which must always include the column with the delimited cells. The last one is named StartRow and it specifies the row containing the first piece of data (that is, it is the row number below the headers, if any).

---

Try This

Code:

---


Sub test()
    Dim rngData    As Range
   
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngData = .Range("A1").CurrentRegion
        rngData.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:=""""
    End With
End Sub

---

Regards
Prince

Hi cjjn,

you can also try the same using text to column wizard. Find below link for help:

Excel Text to column wizard

:cheers: