Parsing one value from 1 cell to multiple cell

Fida

The attached revised file contains this macro (in Module1), with comments to show what happens:

Sub SplitCell()

    Dim Delim As String, InRng As Range, OutRng As Range
    Dim m As Long, n As Long, p As Long

    ' set up divider and where data start and is sent (top left cell)
    Delim = "|"
    Set InRng = Range("A2")
    Set OutRng = Range("F2")

    ' speed up for bigger data sets
    Application.ScreenUpdating = False
    ' clear output
    OutRng.CurrentRegion.Clear

    ' expand top left in to extent of range
    Set InRng = InRng.CurrentRegion

    ' copy header row
    InRng.Rows(1).Copy Destination:=OutRng


    ' set offset counter
    p = 1

    'loop down input range
    For m = 2 To InRng.Rows.Count
        'create an array, splitting text by stated divider
        r = Split(InRng.Cells(m, 2), Delim)
        'loop thorugh that araay
        For n = 0 To UBound(r, 1)
            'write the common values
            OutRng.Offset(p, 0) = InRng.Cells(m, 1)
            'write the split values, one cell to the right
            OutRng.Offset(p, 1) = Trim(r(n))
            ' increment the row counter
            p = p + 1
        Next n
    Next m

    ' write borders for the output
    OutRng.CurrentRegion.Borders.LineStyle = xlContinuous

    ' restore
    Application.ScreenUpdating = True

    MsgBox "Wrote " & p & " rows to " & OutRng.Address(0, 0) & " and down"

End Sub

With this file, press the key combination Ctrl+Shift+J and the macro will run, splitting down column B (in what was your tbl1) and placing the results in F2 and down.

You can change the early portions in bold above to suit different in/ out locations and different delimiters. e.g. you could change Delim = "|" to read Delim = "&"  if your data is separated by the ampersand & instead.

Hope this works for you (and is fast enough with your larger real data). If so, please re,me,mber to mark this Answer as Selected.