Excel: Dividing a column into a table with multiple criteria

I have some barcode scanner which lists out the codes on a column in Excel.

The goal is to divide the original column into a table as per the test cases below. Does anyone have an idea in how to achieve it?

Table Before:

Table After:

I have tried using the Power query but wanted to do it with excel functions. Tried using the WRAPROWS

=WRAPROWS(A2:A300,4)

not working. There is one more problem when the qty is twice for A,B,C column it will be added up. for example as in the screenprints.

Please ask away if you have any questions. I will post the power query code if needed. TIA.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([A], "XXID") then [A] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([A], "UID")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.Length([A]) = 5 then [A] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down1", "Custom.2", each if Text.Length([A]) = 6 then [A] else null),
    #"Filled Down2" = Table.FillDown(#"Added Custom1",{"Custom.2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down2",{"Custom.2"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Up", "Custom.3", each if Text.Length([A]) < 4 then [A] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Custom.3] <> null and [Custom.3] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "A"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Custom", "Custom.1", "Custom.2"}, {{"A", each List.Sum([A]), type nullable text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}})
in
    #"Changed Type2"

Try:

=LET(arr,VSTACK(TOCOL(A:A,1),"Finish"),n,ROWS(arr),seq,SEQUENCE(n),seqA,SEQUENCE(n-1),
WRAPROWS(DROP(REDUCE("",seqA,LAMBDA(all,curr,
LET(length,LEN(INDEX(arr,curr)),lenNext,LEN(INDEX(arr,curr+1)),IF(AND(length<=4,lenNext>4),VSTACK(all,
      XLOOKUP(1,(LEN(arr)=8)*(seq<curr),arr,,,-1),
      XLOOKUP(1,(LEN(arr)=5)*(seq<curr),arr,,,-1),
      XLOOKUP(1,(LEN(arr)=6)*(seq<curr),arr,,,-1),
      SUM(FILTER(arr,(seq<=curr)*(seq>XMATCH(1,(LEN(arr)>4)*(seq<curr),,-1))))
),all)))),1),4))

(Holy moly Tom Sharpe's answer is great! And already accepted. But since I had written this already I thought I'd contribute it.)

I would use List.Accumulate.

let
  // Table6's A column as a list
  Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content][A],
  #"Accumulate Values" = List.Accumulate(
    Source,
    // NextRow is the row we're building
    // Rows is a list of rows we've built
    [
      NextRow = {},
      Rows = {}
    ],
    (_, v) => let
      vnum = try Number.From(v) otherwise null,
      NextRowCount = List.Count([NextRow]),
      ThisColNum = if (vnum is null) then
        // not a number so must be column A
        0
      else if vnum < 10000 then
        // Assume Qty (column D) if less than 5-digit number
        3
      else if vnum < 100000 then
        // Column B if 5-digit number
        1
      else
        // Column C
        2,
      ret = if ThisColNum = NextRowCount then
        // Append v to row we're building
        _ & [NextRow = [NextRow] & {vnum ?? v}]
      else if NextRowCount <> 4 then
        // if v isn't the next column to append to the row we're building then we should have a complete row!
        error Error.Record("Bad Values", "NextRow incomplete", [state=_, value=v])
      else if ThisColNum = 3 then
        // another quantity. Add to column D
        _ & [
          NextRow = List.FirstN([NextRow], 3) & {[NextRow]{3} + vnum}
        ]
      else
        // Commit row we've built, start building new row
        [
          NextRow = List.FirstN([NextRow], ThisColNum) & {vnum ?? v},
          Rows = [Rows] & {[NextRow]}
        ]
      in
        ret
  ),
  // Get all the rows we've built
  Rows = if List.Count(#"Accumulate Values"[NextRow]) = 4 then
    #"Accumulate Values"[Rows] & {#"Accumulate Values"[NextRow]}
  else
    error Error.Record("Bad Values", "Last NextRow incomplete. Last value not quantity?", #"Accumulate Values"),
  Table = #table(
    type table [A=text, B=Int64.Type, C=Int64.Type, D=Int64.Type],
    Rows
  )
in
  Table