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!

 

[VoG]

I'm sorry but I cannot reproduce this behaviour. If a cell in the column to be split contains a space (character 32), that space is simply copied into the output.

 

[cduerson]

I apologize I guess I wasn't clear. If the cell containing the field to be split is blank it works fine, it's the other fields.

For example if column "Q" has a blank (character 32) in row 2 and column "Q" didn't have a blank (character 32) in the row 1 , the previous value from row 1 is copied down until a non-blank value is encountered.

 

[VoG]

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

 

[cduerson]

Peter,
The new code fixed the space issue.

Thanks

I still can't solve the blow up problem.

I have tested the code on 2 other 2003 installs with the same results. Unfortunately I don't have access to 2007.

The articles referenced in the post at
http://www.mrexcel.com/forum/showthread.php?t=11143 which points to a microsoft article here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832

mention that the issues occur when the code makes an unqualified method call or property call to an Office object does your code call any microsoft objects that should be qualified?

 

[VoG]

Are there error values anywhere in your range?

 

[cduerson]

So the problem is in my data. I believe that it is dieing on two records that have either a CR or LF in them. When I retype the data everything works fine except it ends with a 400 code. The data appears to be there though when I manually split the fields and count the total records.

After looking at it though I noticed that some of the data is delimited by a space instead of a comma and the data is locked so I can't go back in and change it.

Is there a way to convert all "CR", "LF", and "," to a " " and then delimit on a space? The data is all server names so there will not be any spaces within the name. This should solve all the problems as I noticed that my data sometimes has a leading space when the original data was a comma and a space between the server names.

I think if you could give me some guidance on the conversion and tell me what is going on with the 400 return code that would be great.

I do appreciate all your help, this code will save me hours of time weekly by not requireing me to mannuall enter data in a tracking sheet that has most of the data already stored in the database.

Thanks

 

[VoG]

OK, try this (my final effort on this question)

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
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = " "
On Error GoTo 0
Columns(iCol).Insert
With Range(Cells(1, iCol + 1), Cells(LR, iCol + 1))
    .Replace What:=",", Replacement:=" ", LookAt:=xlPart
    .Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart
    .Replace What:=Chr(13), Replacement:=" ", LookAt:=xlPart
End With
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

 

[cduerson]

Thank you sir, that is exactly what I needed and works as advertised. It ends with a 400 but all the results are there.

I can't thank you enough for your help, you have saved me countless hours of work.

 

[RCOX]

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.

 

[VoG]

I think that you should start a new thread as this seems to be a different question.

Use Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php to post what you have and what you want to achieve (using dummy data - apples, bananas, ...).