How to take csv list and make new column for each comma?

[respawn]

Hello,

I thought doing a text delimiting function would give me the desired output, but it doesn't. And I have spent a good bit of time googling to no avail.

I want to take a text (csv) file that has data like this:
-----------
Column name1
dataA
dataB
dataC

,Column name2
dataD
dataE

,Column name3
dataF
dataG
dataH
dataI

.
.
.
---------------
And give me an excel spreadsheet like this:

Column name 1 | Column name 2 | Column name 3 | ....
data _____________dataD__________dataF
dataB_____________dataE__________dataG
dataC___________________________dataH
________________________________dataI

----------------
Notice, I am using underscore for formatting since the forums strip out all the white spaces I tried to add.
Your help is extremely appreciated!

 

[FDibbins]

Is that all in 1 cell, or different cells per "set, or all in different cells?
Can you show the actual cvs data?
Have you tried Text2Columns?

 

[respawn]

Is that all in 1 cell, or different cells per "set, or all in different cells?

Can you show the actual cvs data?
Have you tried Text2Columns?

Yes, sorry I wasnt more clear in the initial post, I tried text2columns and used "comma" as the delimiter and it just moves each Column name over 1 column (so they all in B). Not what I want.

Here is an image of what I am talking about:

The cvs is on the left and what I want in excel is on the right. The actual file has about 260 column names (and commas). They are the only commas in the file.

 

[Fluff]

Hi & welcome to the board.
Does this do what you need

Sub RearangeOnComma()

    Dim Ar As Areas
    Dim Cnt As Long
    Dim Col As Long

    Columns(1).Insert
    With Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)
        .Value = Evaluate("if(left(" & .Offset(, 1).Address & "1)="","",1,"""")")
        Set Ar = .SpecialCells(xlBlanks).Areas
    End With
    
    Col = 3
    For Cnt = 2 To Ar.Count
        Ar(Cnt).Offset(-1, 1).Resize(Ar(Cnt).Rows.Count + 1).Cut Cells(1, Col)
        Col = Col + 1
    Next Cnt
    Columns(1).Delete
    
End Sub

 

[respawn]

Hi & welcome to the board.
Does this do what you need

AWESOME WONDERFUL MODULE

YES! OMG! THANK YOU SO MUCH!!!

If you are ever in the New Orleans area, I owe you lunch/dinner!

You are awesome. Thank you 1000000x. Perfect!

 

[Fluff]

Glad to help & thanks for the feedback