Split Cell with multiple delimiters into multiple rows and columns

So I have a cell that looks like this:

Dog; 1, Cat; 2, Bird; 6, Pig; 4

I would like to dump all the animal names (items that come before the ";") into one column and in the column next to it dump all the numbers (items that come before the ","). So in the end it would look something like this:
Column A Column B
Dog 1
Cat 2
Bird 6
Pig 4

I've done splits before but not with multiple delimiters?

Originally Posted by nobodyukno

So I have a cell that looks like this:

Dog; 1, Cat; 2, Bird; 6, Pig; 4

So we don't know which is the cell ? Its address will help us to elaborate an easy way …

Originally Posted by Marc L

So we don't know which is the cell ? Its address will help us to elaborate an easy way …

The cell would be L1 but I can always adjust

Originally Posted by WideBoyDixon

Set the source and target cell as necessary:

Please Login or Register  to view this content.

WBD

Originally Posted by Marc L

In an Excel worksheet array the comma separates columns and the semi column separates rows
but your data in L1 is exactly the opposite so just with the VBA Replace function
that's easy just with a couple of codelines like this beginner starter demonstration :

PHP Code: 

Sub Demo()
    Dim V
        V = Evaluate("{""" & Replace$(Replace$([L1].Text, "; ", ""","), ", ", ";""") & "}")
        [A1].Resize(UBound(V), UBound(V, 2)).Value2 = V
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Both work! Thanks a lot!

You're welcome and thanks for the rep' !

Another way...

Please Login or Register  to view this content.