CODEWITHSUNDEEP
excelexcel-2007vba
James Inkster
James Inkster

Reputation: 11

Excel - Split apart a list

I have a list of about 300 items, that I need spaced out every 8 cells as opposed to being one after the other. I'm sure there is an easy way to do this, however my brain is failing me. I have a feeling my terminology is hurting hence why I can't find an answer.

enter image description here

Upvotes: 1

Views: 191

Answers (3)

lina curious
lina curious

Reputation: 81

=IF(MOD(ROW()+7;8)=0;INDEX(A:A;INT(ROW()/8)+1);"")

Given that data begins at A1 and formula is used from row 1 (coulmn is not important).

Upvotes: 2

Santosh
Santosh

Reputation: 12353

Try below code

Sub Main()


    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To lastRow
        If i = 1 Then
            Cells(i, 5).Value = Cells(i, 1)
        Else
            Cells((i - 1) * 9, 5).Value = Cells(i, 1)
        End If
    Next

End Sub

Output

enter image description here

Upvotes: 1

rwking
rwking

Reputation: 1032

Can you please post a screen shot or add some additional detail?

Two things come to my mind for possible solutions:

1) Use Text to columns (if that is where you're going with this) or

2) Use a formula like =LEFT(A1, 10) in the 8th column and fill down (10 can be changed to whatever the first part of the string is that needs to be separated).

Provide some additional info and I'll take another look!

Upvotes: 0

Related Questions