Separate strings into different cells in excel using VBA

Question

For example, I have a string variable named str. This str has a value of:

apple
orange
pineapple

Each word is separated by a newVbLine. I want to move it on cells. A1 contains apple, A2 contains orange and A3 contains pineapple.

Thanks.

What data range do you want to manipulate - do you have existing data in A2, A3 etc? — brettdj
– brettdj, Commented Apr 1, 2012 at 9:28

brettdj · Accepted Answer · 2012-04-01 09:52:19Z

5

The code below splits strings delimited by vbNewLine in column A into column B.

Please change
ws1.[b1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)
to
ws1.[a1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)

if you want to overwrite column A

Sub Spliced()
    Dim ws1 As Worksheet
    Dim X
    Set ws1 = Sheets(1)
    X = Split(Join(Application.Transpose(ws1.Range(ws1.[a1], ws1.Cells(Rows.Count, "A").End(xlUp))), vbNewLine), vbNewLine)
    ws1.[b1].Resize(UBound(X) - LBound(X) + 1, 1) = Application.Transpose(X)
End Sub

enter image description here

edited Apr 1, 2012 at 9:52

answered Apr 1, 2012 at 9:44

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jayson Tamayo Over a year ago

how do i accept a string variable that contains the text separated by vbNewLine?

brettdj Over a year ago

@JaysonTamayo - doesn't this code split as you desire? It does need to a be a vbNewLine?

Collectives™ on Stack Overflow

Separate strings into different cells in excel using VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related