EXCEL How do I split text into different cells and arrange them vertically?

Question

I want to process a large amount of data. It's not possible to do it manually. I want to make the source(eg 1) into form like(eg 2).

[eg 1]
a1;a2;a3
b1;b2;b3;b4
c1;c2

[eg 2]
a1
a2
a3
b1
b2
b3
b4
c1
c2

However, I can only be able to transform it into this form.
a1 a2 a3
b1 b2 b3 b4
c1 c2

=============================================================================== Thank you very much. And I have another thing to do. I want to do it more advance.

let x be any # and here be 21

[eg 1]
a1;a2;a3
b1;b2;b3;b4
c1;c2

[eg 2]
21 a1
21 a2
21 a3
22 b1
22 b2
22 b3
22 b4
23 c1
23 c2

space means that they are in different cells.

Thanks~~

Answer 1

Assuming a1;a2;a3 is in cell A1 , try this small macro to place the results in column B :

Sub ReArrange()
    Dim N As Long, i As Long, BigString As String
    N = Cells(Rows.Count, 1).End(xlUp).Row
    BigString = Range("A1").Value
    For i = 2 To N
        BigString = BigString & ";" & Cells(i, 1).Value
    Next i
    i = 1
    ary = Split(BigString, ";")
    For Each a In ary
        Cells(i, 2).Value = a
        i = i + 1
    Next a
End Sub

EDIT#1:

based on your need for the extra column, this macro asks for the initial value and places the results in columns A and B :

Sub ReArrangeII()
    Dim N As Long, i As Long, BigString As String
    Dim M As Long, j As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    M = Application.InputBox(Prompt:="enter initial value", Type:=1)
    j = 1
    For i = 1 To N
        BigString = Cells(i, 1).Value
        ary = Split(BigString, ";")
        u = UBound(ary) + 1
        For Each a In ary
            Cells(j, 2).Value = M
            Cells(j, 3).Value = a
            j = j + 1
        Next a
        M = M + 1
    Next i
End Sub