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

[Bond007]

Hello - could anyone help me? I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:

A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...

What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with

A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.

Many Thanks!

 

[RCOX]

Thanks for the fast response VoG. I don't have administrative rights on this computer and can't install Excel Jeanie. I will do my best to repost in a new thread with a manually created dummy spreadsheet.

 

[VaughanJer]

Try

Option Explicit

Sub Splt()
Dim LR As Long, i As Long, LC As Integer
Dim x As Variant
Dim r As Range, iCol As Integer
On Error Resume Next
Set r = Application.InputBox("Click in the column to split by", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = " "
On Error GoTo 0
iCol = r.Column
Application.ScreenUpdating = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, iCol).End(xlUp).Row
Columns(iCol).Insert
For i = LR To 1 Step -1
    With Cells(i, iCol + 1)
        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(iCol + 1).Delete
LR = Cells(Rows.Count, iCol).End(xlUp).Row
With Range(Cells(1, 1), Cells(LR, LC))
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
With ActiveSheet.UsedRange
    .Replace What:=" ", Replacement:=vbNullString, LookAt:=xlWhole
End With
Application.ScreenUpdating = True
End Sub

VoG,

This code works for me and has been a huge help for a long time. I export spreadsheets from Access to Excel, and I just ran into a bug that I never hit before.

It appears that the code stops "splitting" the specified cell range once it hits 255 characters in a cell. For example, I have a cell that has 500-600 characters, but it only splits up to the 255 character in the cell and then continues on and splits the rest of the smaller cells.

Any ideas on how to quickly fix this? I realize this thread is old, but my question is directly related to the code you provided so I figured this would be the proper place to ask the question.

Thanks!
Jeremy Vaughan

 

[excel08]

Can anyone please let me know as to how can I change the delimeter based on which the split up criteria can be changed.

Thanks in Advance
Alok

 

[excel08]

I have excel sheet with lot of test cases and in one of the cells the steps for the test cases are mentioned.

My test case steps look something like
1. abc
2. xyz
3. lmno

So I am trying to split up the steps in separately in each cell.

In this code I could not figure out which part serves the purpose of delimeter used for splitting the code.

Thanks
Alok

 

[Legacy 103420]

Many thanks VoG for this code! I'm having one trouble that I now spent an hour trying to figure out to no avail.

In my sheet I have a formula and whenever I run this macro the formula becomes #REF. Any idea why this is?

Thank you

 

[cjjn]

Hate to bump such an old thread for my first post but I have a similiar issue and I figure it's better than creating a new thread. I tried some of the code posted and it helped but unfortately I have multiple columns with comma delimiters and I cannot figure out how to tweak the code.

I really cannot divulge too much information from my file as it is sensitive material and I appologize in advance.

There are about 820 unique tools on the list with anywhere from 1-4 seperate orders for each. Unfortunately, they decided to condense the list before I started on the project and have ONE row for each tool - seperating the other fields with comma delimiters (why they didn't try to find a way to group with drop-downs is beyond me).
For every tool ordered, a notification is tied to an order number and a purchase requistion number. These numbers are uniquely matched and in respective order in each cell. A fourth value is the equiptment number and is in respective order but there can be multiple tools on the same notification/order/purchase requisition resulting in multiple. If a row has 2 notifications numbers (J), 2 order numbers (K), 2 purch req numbers (L), and (2) equiptment numbers (M) then they can be safely seperated into two rows. If say it has: 2 notifications numbers , 2 order numbers , 2 purch req numbers, and 8 equiptment numbers then it could be any combination (1 and 7, 4 and 4, 3 and 5 etc...) and I would need these rows highlighted and I could check the system and fix it manually (there are not too many that have multiple tools on the same order. Note, there is a column (E) that has Quantity which matches the total number of equiptment numbers listed for each tool.

So, basically I need to go from a unique row for every tool to a unique row for every notification (with the uniquely matched order/purch req and for the most part, equiptment numbers matching).
Columns A,B,C,D,E can all be copied to the new rows as these values are tied to the tool, not the order. G,H,I and N are blank and are only useful after successfully seperating (I can delete them for now if that makes it easier). J, K, L, and M are the ones with commas that need to be seperated.

I know this may be a tall task and I applogize for being so vague - if more information is required I would be more inclined to share it via PM. My plan was, if this is do-able (I know it is I am just not versed with VBA) is to separate it all out and then group by duplicate Tool Numbers so that if you want to see the multiple orders it is just a drop down. I think that will be the easy part.

Thanks in advance and hopefully you experts out there can help me fix this awful Excel structure.

Hi - I need the same thing you spoke of - but cannot find the new thread can you please send me a link?

 

[keith11]

I am trying to do something similar to the OP, but since I lack skills in VB, I am not able to modify any of the codes posted here for my needs. Basically, I am trying to generate rows for text separated by a period or a comma in cells.

Input looks like this:

Heading 1        Heading 2         Heading 3        Heading 4
 value 1             value 2             value 3     a,b,c (or a.b.c)

The output should look like:

Heading 1  Heading 2    Heading 3   Heading 4
 value 1       value 2     value 3            a
 value 1        alue 2      value 3            b
 value 1       value 2      value 3           c

I would greatly appreciate if someone can help with a macro which would accomplish the above. Please note again, that I am not skilled in VB. Also, instead of a comma, the values under Heading 4 can be separated by a period ("."), so what would I need to change in the code for periods? Thanks!

 

[Peter_SSs]

Keith
Welcome to the MrExcel board!

Assuming data (Heading) starts in A1, no blanks in column D, and results to go into columns(E:H), try this in a copy of your workbook.

If there are any problems, post back with more details, including about how many rows in the original data.

Sub Rearrange()
  Dim Bits
  Dim nr As Long, lr As Long, rws As Long
  Dim c As Range
  
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("E1:H1").Value = Range("A1:D1").Value
  nr = 2
  For Each c In Range("A2:A" & lr)
    Bits = Split(Replace(c.Offset(, 3).Value, ".", ",", 1, -1, 1), ",")
    rws = UBound(Bits) + 1
    Cells(nr, "H").Resize(UBound(Bits) + 1).Value = Application.Transpose(Bits)
    Cells(nr, "E").Resize(UBound(Bits) + 1, 3).Value = c.Resize(, 3).Value
    nr = nr + rws
  Next c
  Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

I am trying to do something similar to the OP, but since I lack skills in VB, I am not able to modify any of the codes posted here for my needs. Basically, I am trying to generate rows for text separated by a period or a comma in cells.

Input looks like this:

Heading 1        Heading 2         Heading 3        Heading 4
 value 1             value 2             value 3     a,b,c (or a.b.c)

The output should look like:

Heading 1  Heading 2    Heading 3   Heading 4
 value 1       value 2     value 3            a
 value 1        alue 2      value 3            b
 value 1       value 2      value 3           c

I would greatly appreciate if someone can help with a macro which would accomplish the above. Please note again, that I am not skilled in VB. Also, instead of a comma, the values under Heading 4 can be separated by a period ("."), so what would I need to change in the code for periods? Thanks!

I have an article in my mini-blog site that can do what you ask. Here is the link...

Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

You will have to change the assignments being made in these three Const statements...

Const Delimiter As String = ", "
Const DelimitedColumn As String = "C"
Const TableColumns As String = "A:C"

to this for the data you posted...

Const Delimiter As String = ","
Const DelimitedColumn As String = "D"
Const TableColumns As String = "A:D"

 

[keith11]

Keith
Welcome to the MrExcel board!

Assuming data (Heading) starts in A1, no blanks in column D, and results to go into columns(E:H), try this in a copy of your workbook.

If there are any problems, post back with more details, including about how many rows in the original data.

Sub Rearrange()
  Dim Bits
  Dim nr As Long, lr As Long, rws As Long
  Dim c As Range
  
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("E1:H1").Value = Range("A1:D1").Value
  nr = 2
  For Each c In Range("A2:A" & lr)
    Bits = Split(Replace(c.Offset(, 3).Value, ".", ",", 1, -1, 1), ",")
    rws = UBound(Bits) + 1
    Cells(nr, "H").Resize(UBound(Bits) + 1).Value = Application.Transpose(Bits)
    Cells(nr, "E").Resize(UBound(Bits) + 1, 3).Value = c.Resize(, 3).Value
    nr = nr + rws
  Next c
  Application.ScreenUpdating = True
End Sub

Thanks, Peter. I tried your code, with modified values for the range and such, but the code threw an error #13. I think I should clarify further what I am trying to do.

I have the following structure for my data:

input:

Heading 1 (cell A4)    heading 2 (cell b4)  Heading 3 (cell c4)   Heading 4 (cell d4)  Heading 5 (cell e4)  Heading 6 (cell I4)
 value 1                     value 2                   value 3                      value 4                  value 5                      a,b,c (or a.b.c)

The output needs to look like:

Heading 1 (cell A4)    heading 2 (cell b4)  Heading 3 (cell c4)   Heading 4 (cell d4)  Heading 5(cell e4)  Heading 6 (cell I4)
 value 1                     value 2                   value 3                      value 4                 value 5                      a
 value 1                     value 2                   value 3                      value 4                 value 5                      b
 value 1                     value 2                   value 3                      value 4                 value 5                      c

I am not sure how to make this text box retain the formatting in the code above, as the values are not staying aligned with their columns, but I hope you can understand what I am trying to show.

I have about 50 rows of data, but I expect about 700-1000 rows of data in the next month. Also, some cells in column I are blank.

I hope this information helps. Is it possible to replace the content of the original cells instead of migrating the data to a new set of columns? Looking forward to your response.