Arranging continuous text into rows and columns

I receive a data file "input" sheet in the attached file. I have to convert this into as in the "output" sheet. Is there any coding for this.

Your result looks strange to me, or am I missing something?

Sub test()
    Dim a, b, i As Long, n As Long, e
    a = Sheets("input").Cells(1).CurrentRegion.Value
    ReDim b(1 To Rows.Count, 1 To 3)
    For i = 2 To UBound(a, 1)
        For Each e In Split(a(i, 2), ",")
            n = n + 1: b(n, 1) = a(i, 1)
            b(n, 2) = Trim$(Split(e, "(")(0))
            b(n, 3) = Val(Split(e, "(")(1))
    Next e, i
    With Sheets.Add.Cells(1).Resize(, 3)
        .Resize(, 2).Value = a
        .Rows(2).Resize(n).Value = b
    End With
End Sub

Greetings,
Perfect solution. You have not missed anything. What I require was to separate the attribute value ( #) from the asset (Transformers) and the place (station).

Thanks
Rajesh