If your data looks like this:
Please find the VBA Code to accomplish your task below. I have commented it for better understanding:
Sub Test()
Dim LngLp As Long
Dim ShrtLp As Integer
Dim SrcSht As Worksheet
Dim DestSht As Worksheet
Dim ColCounter As Integer
Dim SrcLrow As Long
Dim DestLrow As Long
Set SrcSht = Sheets("Before") 'Define Worksheet
Set DestSht = Sheets("After") 'Define Worksheet
SrcLrow = SrcSht.Cells(Rows.Count, "A").End(xlUp).Row 'Define Source LastRow
'Cycle through column A on Source SHeet and look for @
For LngLp = 1 To SrcLrow
If SrcSht.Cells(LngLp, "A") = "@" Then 'If we find an @ move data to After Worksheet
DestLrow = DestSht.Cells(Rows.Count, "A").End(xlUp).Row 'Define Destination Sheet Last Row
For ShrtLp = LngLp + 1 To SrcLrow
If SrcSht.Cells(ShrtLp, "A") <> "@" Then 'Identify if this is the last field in the address
ColCounter = ColCounter + 1 'We need to increment columns in order to make sure the data is moved correctly
DestSht.Cells(DestLrow + 1, ColCounter) = SrcSht.Cells(ShrtLp, "A")
Else
LngLp = ShrtLp - 1 'Reset the LngLp Value to start on the next address
ColCounter = 0 'Reset Column Counter
Exit For
End If
Next ShrtLp
End If
Next LngLp
End Sub
Another variant is with Power Query
Generated script
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
IndexSeparator = Table.AddColumn(AddIndex, "Custom",
each if [Column1] = "@" then [Index] else null),
IndexGroups = Table.FillDown(IndexSeparator,{"Custom"}),
RemoveSeparator = Table.SelectRows(IndexGroups, each ([Column1] <> "@")),
GroupTexts = Table.Group(RemoveSeparator, {"Custom"},
{{"Combined", each Text.Combine([Column1], " "), type text}}),
KeepIt = Table.SelectColumns(GroupTexts,{"Combined"})
in
KeepIt