Вот ваше решение. В этом примере образец данных находится в ячейках B2:B17. Чтобы это решение работало, вы должны оставить одну пустую ячейку перед данными и после данных, т.е. в этом случае B1 и B18. Предполагается, что между диапазонами есть только одна пустая ячейка, иначе решение может работать некорректно.
Если ваша версия Excel не имеет функции TEXTJOIN, используйте ниже UDF в VBA для ее создания. Нажмите ALT + F11 для доступа к VBA Editor. Вставьте модуль из меню вставки. Дважды щелкните имя модуля в левой панели, чтобы открыть его редактор кода.
Вставьте в него следующий код.
Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN1 = Left(result, Len(result) - Len(delimiter))
End Function
Сохраните файл как Macro Enabled WOrkbook XLSM, если у вас Excel 2007 и выше.
В ячейку C1 поместите следующую формулу и перетащите ее вниз до длины данных столбца. Как уже упоминалось выше, первая и последняя ячейка должны быть пустыми.
=IF(ISBLANK(B1),TEXTJOIN1(",",FALSE,OFFSET(B1,1,0):OFFSET(B1,MIN(IF(ISBLANK(B2:$B$18),ROW(B2:$B$18)-ROW(B2),9^99)),0)),"")
Нажмите CTRL + SHIFT + ENTER в пределах панели формул, чтобы создать формулу массива. Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.
Теперь перетащите его вниз по длине столбца.