My cells look like this:

I want them to look like this with spacing between cells that need it to accommodate the repairs that were completed:

If there is any other information that would help, I would be grateful!

CodePudding user response：

try:

=ARRAYFORMULA({SPLIT(FLATTEN(SPLIT(QUERY(
 IF(D1:D="",,"×"&TO_TEXT(A1:A)&"♦"&B1:B&"♦"&C1:C&REPT("× ", 
 LEN(REGEXREPLACE(D1:D, "[^,]", )))),,9^9), "×")), "♦"), 
 QUERY(TRIM(FLATTEN(IFERROR(SPLIT(D1:D, ",")))), "where Col1 is not null", )})

update:

=ARRAYFORMULA({SPLIT(FLATTEN(SPLIT(QUERY(
 IF((D1:D="")*(A1:A=""),,"×"&TO_TEXT(A1:A)&"♦"&B1:B&"♦"&C1:C&REPT("× ", 
 LEN(REGEXREPLACE(D1:D, "[^,]", )))),,9^9), "×")), "♦"), 
 SUBSTITUTE(QUERY(TRIM(FLATTEN(IFERROR(SPLIT(IF((D:D="")*(A:A<>""),"¤",D1:D), ",")))), 
 "where Col1 is not null", ), "¤", )})