Problem description & analysis

Below is Excel file data:

Data in column A is divided into blocks by empty row. Each block contains N rows plus one empty row. We are trying to add a new column B. Strings of N rows in each block will be connected by hyphens and entered in the empty rows while the other rows in column B will remain empty. The desired result is as follows:

Solution

Use SPL XLL plug-in of Excel

Write the formula in cell B1:

=spl("=?.conj().(int(~)).group@i(~[-1]==null).([null]*(~.len()-1)|~.to(~.len()-1).concat(""-"")).conj()",A1:A28)

As shown:

Explanation:

Create a new group if the previous member is a null string and then remove the null string from each group. Join members of each group into a string with the hyphen -, count members and add empty strings of same number before the pieced “string”.

Q & A Collection

https://stackoverflow.com/questions/63754291/combining-cell-values-with-vba