How to split single text cell into multiple rows, using a comma delimiter? - Page 10
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 10 of 10 FirstFirst ... 8910
Results 91 to 99 of 99

Thread: How to split single text cell into multiple rows, using a comma delimiter?

  1. #91
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

     
    Quote Originally Posted by VoG View Post
    This splits column M. I've tested this with over 200 rows of data with no error (Excel 2007).

    Code:
    Sub Splt()
    Dim LR As Long, i As Long
    Dim X As Variant
    Application.ScreenUpdating = False
    LR = Range("M" & Rows.Count).End(xlUp).Row
    Columns("M").Insert
    For i = LR To 1 Step -1
        With Range("N" & i)
            If InStr(.Value, ",") = 0 Then
                .Offset(, -1).Value = .Value
            Else
                X = Split(.Value, ",")
                .Offset(1).Resize(UBound(X)).EntireRow.Insert
                .Offset(, -1).Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
            End If
        End With
    Next i
    Columns("N").Delete
    LR = Range("M" & Rows.Count).End(xlUp).Row
    With Range("A1:Q" & LR)
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        On Error GoTo 0
        .Value = .Value
    End With
    Application.ScreenUpdating = True
    End Sub

    Worked like a charm

  2. #92
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Hi Guys,

    Sorr to bring up a old thread.

    I am new to this forum. And need some help from you all.

    I need to split some data in a cell to different rows and try some of the codes given but unsuccessful.

    example as below:
    Raw Data
    Rooms Date Start End Day Weeks Event
    RM901/RM902; RM903/RM904 11/04/2017 12:30 PM 2:30 PM Tuesday 16 FOOTBALL
    Desired Outcome
    Rooms Date Start End Day Weeks Event
    RM901 11/04/2017 12:30 PM 2:30 PM Tuesday 16 FOOTBALL
    RM902 11/04/2017 12:30 PM 2:30 PM Tuesday 16 FOOTBALL
    RM903 11/04/2017 12:30 PM 2:30 PM Tuesday 16 FOOTBALL
    RM904 11/04/2017 12:30 PM 2:30 PM Tuesday 16 FOOTBALL

    Please advise.. and thanks in advance.. my current raw data can go up to 100000 rows

    Cheers

  3. #93
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Give this a try if about 100 sec to process 100.000 cells is OK. If not, somebody better with arrays will certainly help.

    Code:
    Sub Sep()
    Dim c As Range
        For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
            c = Replace(c, ";", "/")
            x = Split(c, "/")
                For i = 0 To UBound(x)
                    Cells(2 + y + i, 9) = x(i)
                    Cells(2 + y + i, 10) = Cells(2 + k, 2)
                    Cells(2 + y + i, 11) = Cells(2 + k, 3)
                    Cells(2 + y + i, 12) = Cells(2 + k, 4)
                    Cells(2 + y + i, 13) = Cells(2 + k, 5)
                    Cells(2 + y + i, 14) = Cells(2 + k, 6)
                    Cells(2 + y + i, 15) = Cells(2 + k, 7)
                Next
                y = y + i: k = k + 1
        Next
    End Sub
    Last edited by István Hirsch; Nov 30th, 2017 at 05:48 AM.

  4. #94
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,831
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Quote Originally Posted by István Hirsch View Post
    Give this a try if about 100 sec to process 100.000 cells is OK. If not, somebody better with arrays will certainly help.

    Code:
    Sub Sep()
    Dim c As Range
        For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
            c = Replace(c, ";", "/")
            x = Split(c, "/")
                For i = 0 To UBound(x)
                    Cells(2 + y + i, 9) = Trim(x(i))
                    Cells(2 + y + i, 10) = Cells(2 + k, 2)
                    Cells(2 + y + i, 11) = Cells(2 + k, 3)
                    Cells(2 + y + i, 12) = Cells(2 + k, 4)
                    Cells(2 + y + i, 13) = Cells(2 + k, 5)
                    Cells(2 + y + i, 14) = Cells(2 + k, 6)
                    Cells(2 + y + i, 15) = Cells(2 + k, 7)
                Next
                y = y + i: k = k + 1
        Next
    End Sub
    Your code is preserving a leading blank space in front of some of the room name... it comes from the blank space after the semi-colons. The suggested change shown above in red will fix the problem.

    Your code takes about 120 seconds on my computer to process 100,000 rows of data. In an effort to try to speed things up, I develope the following code which does all of the processing in memory before blasting the results to the worksheet all at once... the code took about 5 seconds to do that.
    Code:
    Sub SeparateRooms() Dim R As Long, c As Long, x As Long, z As Long, LastRow As Long, TotalRooms As Long Dim Rooms As Variant, StaticData As Variant, Result As Variant, Rms() As String LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rooms = Range("A2:A" & LastRow) StaticData = Range("B2:G" & LastRow) TotalRooms = Evaluate(Replace("SUM(IF(A2:A#="""","""",1+LEN(A2:A#)-LEN(SUBSTITUTE(SUBSTITUTE(A2:A#,"";"",""/""),""/"",""""))))", "#", LastRow)) ReDim Result(1 To TotalRooms, 1 To 7) For R = 1 To UBound(Rooms) Rms = Split(Replace(Rooms(R, 1), ";", "/"), "/") For x = 0 To UBound(Rms) z = z + 1 Result(z, 1) = Trim(Rms(x)) For c = 1 To UBound(StaticData, 2) Result(z, c + 1) = StaticData(R, c) Next Next Next Range("J2").Resize(UBound(Result, 1), UBound(Result, 2)) = Result Range("L2:M" & TotalRooms).NumberFormat = "h:mm AM/PM" End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #95
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Quote Originally Posted by Rick Rothstein View Post
    Your code is preserving a leading blank space in front of some of the room name... it comes from the blank space after the semi-colons. The suggested change shown above in red will fix the problem.

    Your code takes about 120 seconds on my computer to process 100,000 rows of data. In an effort to try to speed things up, I develope the following code which does all of the processing in memory before blasting the results to the worksheet all at once... the code took about 5 seconds to do that.
    Code:
    Sub SeparateRooms() Dim R As Long, c As Long, x As Long, z As Long, LastRow As Long, TotalRooms As Long Dim Rooms As Variant, StaticData As Variant, Result As Variant, Rms() As String LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rooms = Range("A2:A" & LastRow) StaticData = Range("B2:G" & LastRow) TotalRooms = Evaluate(Replace("SUM(IF(A2:A#="""","""",1+LEN(A2:A#)-LEN(SUBSTITUTE(SUBSTITUTE(A2:A#,"";"",""/""),""/"",""""))))", "#", LastRow)) ReDim Result(1 To TotalRooms, 1 To 7) For R = 1 To UBound(Rooms) Rms = Split(Replace(Rooms(R, 1), ";", "/"), "/") For x = 0 To UBound(Rms) z = z + 1 Result(z, 1) = Trim(Rms(x)) For c = 1 To UBound(StaticData, 2) Result(z, c + 1) = StaticData(R, c) Next Next Next Range("J2").Resize(UBound(Result, 1), UBound(Result, 2)) = Result Range("L2:M" & TotalRooms).NumberFormat = "h:mm AM/PM" End Sub
    Thanks guys.. it works.. 100 sec or better 5 sec definitely beats me splitting the data manually by days... sure save me loads of time... greatly appreciated..

    Hi Rick, if i use your code... and i have additional columns to be added in (some other booking details and remarks), maybe to column J.. do i just change StaticData = Range("B2:J" & LastRow)?

    Thanks again guys

  6. #96
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,831
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Quote Originally Posted by Yakkie View Post
    Hi Rick, if i use your code... and i have additional columns to be added in (some other booking details and remarks), maybe to column J.. do i just change StaticData = Range("B2:J" & LastRow)?
    Yes, that should be all you need to do.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #97
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Quote Originally Posted by Rick Rothstein View Post
    Yes, that should be all you need to do.
    Hi Rick, seems that i run into
    run-time error '9'
    Subsciprt out of range

    anything else i missed out?

  8. #98
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,831
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

    Quote Originally Posted by Yakkie View Post
    Hi Rick, seems that i run into
    run-time error '9'
    Subsciprt out of range

    anything else i missed out?
    Nope, you didn't miss anything... I did. I accidentally had left a hard-coded number in my code that should have been converted to an expression which calculates the number of columns to resize the Result array to. Here is the corrected code... you still only need to change the line of code you identified for yourself.
    Code:
    Sub SeparateRooms() Dim R As Long, c As Long, x As Long, z As Long, LastRow As Long, TotalRooms As Long Dim Rooms As Variant, StaticData As Variant, Result As Variant, Rms() As String LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rooms = Range("A2:A" & LastRow) StaticData = Range("B2:G" & LastRow) TotalRooms = Evaluate(Replace("SUM(IF(A2:A#="""","""",1+LEN(A2:A#)-LEN(SUBSTITUTE(SUBSTITUTE(A2:A#,"";"",""/""),""/"",""""))))", "#", LastRow)) ReDim Result(1 To TotalRooms, 1 To UBound(StaticData, 2) + 1) For R = 1 To UBound(Rooms) Rms = Split(Replace(Rooms(R, 1), ";", "/"), "/") For x = 0 To UBound(Rms) z = z + 1 Result(z, 1) = Trim(Rms(x)) For c = 1 To UBound(StaticData, 2) Result(z, c + 1) = StaticData(R, c) Next Next Next Range("J2").Resize(UBound(Result, 1), UBound(Result, 2)) = Result Range("L2:M" & TotalRooms).NumberFormat = "h:mm AM/PM" End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #99
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to split single text cell into multiple rows, using a comma delimiter?

      
    Quote Originally Posted by Rick Rothstein View Post
    Nope, you didn't miss anything... I did. I accidentally had left a hard-coded number in my code that should have been converted to an expression which calculates the number of columns to resize the Result array to. Here is the corrected code... you still only need to change the line of code you identified for yourself.
    Code:
    Sub SeparateRooms() Dim R As Long, c As Long, x As Long, z As Long, LastRow As Long, TotalRooms As Long Dim Rooms As Variant, StaticData As Variant, Result As Variant, Rms() As String LastRow = Cells(Rows.Count, "A").End(xlUp).Row Rooms = Range("A2:A" & LastRow) StaticData = Range("B2:G" & LastRow) TotalRooms = Evaluate(Replace("SUM(IF(A2:A#="""","""",1+LEN(A2:A#)-LEN(SUBSTITUTE(SUBSTITUTE(A2:A#,"";"",""/""),""/"",""""))))", "#", LastRow)) ReDim Result(1 To TotalRooms, 1 To UBound(StaticData, 2) + 1) For R = 1 To UBound(Rooms) Rms = Split(Replace(Rooms(R, 1), ";", "/"), "/") For x = 0 To UBound(Rms) z = z + 1 Result(z, 1) = Trim(Rms(x)) For c = 1 To UBound(StaticData, 2) Result(z, c + 1) = StaticData(R, c) Next Next Next Range("J2").Resize(UBound(Result, 1), UBound(Result, 2)) = Result Range("L2:M" & TotalRooms).NumberFormat = "h:mm AM/PM" End Sub
    It works!!! Thanks alot Rick!!!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com