Results 1 to 3 of 3

Thread: Split Row Data In To Multiple Columns Based On Delimiter

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    1
    Rep Power
    0

    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 View Post
    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).
    Attached Images Attached Images

  2. #2
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    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

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Hi cjjn,

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

    Excel Text to column wizard


Similar Threads

  1. Split Text with Delimiter
    By jaffey in forum Excel Help
    Replies: 1
    Last Post: 06-07-2013, 02:25 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  4. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  5. Split Range into Multiple Columns VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 03-07-2012, 10:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •