CONCATENATION+SUBSTITUTE+CHAR(10): keep data in 4 different cells lined up data in 4 other cells

[itr674]

First, I don't have capability to put an Excel sample table from this computer.

I have data in range C8:J8.

Number codes are in range C8:F8
Dates are in range G8:J8.

in cell A8 I capture the data in range C8:F8 with this formula

=SUBSTITUTE(
C8&CHAR(10)&
D8&CHAR(10)&
E8&CHAR(10)&
F8,CHAR(10)&CHAR(10),"")

Now I need to capture the dates in cell B8 from range G8:J8, but if a date is missing in rang G9:J8 I would like for the other dates to line up with the numbers captured in cell A8.

 

[bobsan42]

Depending on your excel version you can use textjoin().
But if blank dates do not correspond to blank numbers you will probably need a helper column. Or many IFs.

 

[FormR]

but if a date is missing in rang G9:J8 I would like for the other dates to line up with the numbers captured in cell A8.

Hi, this assumes that if there is no data in the cell in the range C8:F8 then the corresponding cell in the range G8:J8 is either also blank or doesn't need to be included in the result.

Replace you formula in A8 with:
=MID(IF(C8="","",CHAR(10)&C8)&IF(D8="","",CHAR(10)&D8)&IF(E8="","",CHAR(10)&E8)&IF(F8="","",CHAR(10)&F8),2,9999)

And in B8 try:
=MID(IF(C8="","",CHAR(10)&G8)&IF(D8="","",CHAR(10)&H8)&IF(E8="","",CHAR(10)&I8)&IF(F8="","",CHAR(10)&J8),2,9999)