CODEWITHSUNDEEP
excelvba
MHL
MHL

Reputation: 59

How can I separate a list of things inside one cell into several cells?

I have this cell that has a list of things, for example:

[dogs, cats, mice, cows, horses]

And I want to separate them in different cells:

[dogs]
[cats]
[mice]
[cows]
[horses]

Can this be done?

Upvotes: 1

Views: 1328

Answers (3)

Jeremy
Jeremy

Reputation: 1337

This would do it - You have to select the cell with the cell before running it and it assumes that there is closed brackets ("[" and "]") on either end.

I'll have my best answer now..

Sub ImAmazing()
Dim sString As String, i As Long

If Trim(ActiveCell.Value) = "" Then Exit Sub

ActiveCell.Value = Mid(ActiveCell.Value, 2, Len(ActiveCell.Value) - 2)

Do Until InStr(ActiveCell.Value, ",") = 0
    i = i + 1
    Cells(ActiveCell.Row + i, ActiveCell.Column).Value = "[" & Left(ActiveCell.Value, InStr(ActiveCell.Value, ",") - 1) & "]"
    ActiveCell.Value = Right(ActiveCell.Value, (Len(ActiveCell.Value) - InStr(ActiveCell.Value, ",") - 1))
Loop

ActiveCell.Value = "[" & ActiveCell.Value & "]"

End Sub

Upvotes: 0

Patrick G.
Patrick G.

Reputation: 15

You can easily do this in VBA:

Sub splitString ()
    Dim ran, splitS() As String
    ran = Range("A1")
    splitS() = Split(ran, ",")
    For j = LBound(splitS) To UBound(splitS)
    Range("B" & (j + 1)) = splitS(j)
    Next j
End Sub

If you also want the square brackets, use this code below:

Sub splitStringWithSquareBrackets()
    Dim ran, splitS() As String
    ran = Range("A1")
    ran = Right(ran, Len(ran) - 1)
    ran = Left(ran, Len(ran) - 1)
    splitS() = Split(ran, ",")
    For j = LBound(splitS) To UBound(splitS)
    Range("B" & (j + 1)) = "[" & splitS(j) & "]"
    Next j
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

With data in cell A1:

Sub dural()
    Dim s As String, i As Long

    s = Range("A1").Value
    s = Mid(s, 2, Len(s) - 2)
    ary = Split(s, ", ")
    i = 2

    For Each a In ary
        Cells(i, "A").Value = "[" & a & "]"
        i = i + 1
    Next a
End Sub

enter image description here

Upvotes: 0

Related Questions