chr 10

[remy988]

hi,

i have a string that is separated by chr(10):

Range("c2") = "BIG BEAR" & Chr(10) & "BIG APPLE" & Chr(10) & "BLUE BUG"

how do i extract the strings and list them in range("d2:d4")


thanks
rem

[remy988]

i don't know how to edit my first post.

i found this code
Dim splitVals As Variant
Dim totalVals As Long

splitVals = Split(Range("c11").Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(1, 5 + 1), Cells(1, 5 + 1 + totalVals)).Value = splitVals

that places the data on row 1 beginning in column 5 through the number of columns that the split finds.

how can i list the data down instead of across?

thanks
rem

[remy988]

sorry for the repost. i have it figured out.


rw = 1
For i = LBound(splitVals) To UBound(splitVals)
Range("d" & rw) = splitVals(i)
rw = rw + 1
Next i

[mancubus]

hi.
you dont need a loop here.


Sub split_to_range()

Dim splitVals As Variant

splitVals = Split(Range("C2"), Chr(10))
Range("D2").Resize(UBound(splitVals) + 1) = Application.Transpose(splitVals) 'same column, ie, write to rows. Range("D2:D4") populated.
'Range("D2").Resize(, UBound(splitVals) + 1) = splitVals ''same row, ie, write to columns. Range("D2:F2") populated.


End Sub

[snb]

nor a variable:


Sub split_to_range()
Range("D2").Resize(UBound(split(cells(2,3),vblf)) + 1) = Application.Transpose(split(cells(2,3),vblf))
End Sub

[remy988]

thanks mancubus & snb