An alternative to VBA is Power Query/Get and Transform. You can import the table into PQ. You can then unpivot the column in Question. Save the file to excel and import it back into Access. Here is the MCode for PQ
Code:
let
Source = Excel.CurrentWorkbook(){[Name="TblOriginal"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "
[ListOfThings]", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"
[ListOfThings].1", "
[ListOfThings].2", "
[ListOfThings].3", "
[ListOfThings].4"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"
[ListOfThings].1", type text}, {"
[ListOfThings].2", type text}, {"
[ListOfThings].3", type text}, {"
[ListOfThings].4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"[EventNumber] ", "[AnotherField] "}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"