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

Bond007

New Member
Joined
Dec 1, 2008
Messages
2
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!
 
I have some cells in my dataa that are blank and it looks like a variable is not getting cleared out because the previous data is being copied to the previously blank cells.

This addresses the issue of blank cells in the column to be split. Please note that it replaces blanks with spaces in the output

Code:
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
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 .Value = "" Then
            .Offset(, -1).Value = " "
        ElseIf 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
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Just tried that with no luck.

I tried debug and stepping thru the code to see where it is dieing but it ends up with all cells showing #REF and it looping back thru the prompt for column to split.
 
Upvote 0
I'm beginning to think that there is a problem with your installation of Excel.
 
Upvote 0
@cduerson

I agree with VoG, which is why I suggest you try to repair your office installation. Follow the instructions here.

I can't guarantee it will fix everything for you, but it wouldn't surprise me if it did either.
 
Upvote 0
So I have tried the new code and it doesn't work for me. I have tried to look at the code but must admit I'm very rusty at coding.

I'm not familiar with the debug function to see if it is making it into the code. When I go to debug it I get the debugger to step into the code however I end up with all the cells showing #REF and it prompts a second time for the column to split.

Sorry to be such a pain
 
Upvote 0
Upvote 0
I tried that piece of code, and still get the Null cells filled in with the previous cell values. If I replace all the null cells with a " " it copies the " ".

I also still get the exception with more than 40 rows still trying to track that down.
 
Upvote 0
Say M4 (before running the macro) appears to be blank, enter this in a spare cell

=CODE(M4)

what does it return?
 
Upvote 0
In my data the cell at Q1 contains "Platform" and the cell at Q2 contains "32" as a result of =code(Q2). When the macro runs I get "Platform" in Q2 as well.

I'm trying to track down this "-2147417848 (80010108)" error and it is very elusive, there doesn't seem to be any real answer but I'm still looking.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top