Общая формула
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")
Резюме
Чтобы объединить несколько значений ячеек с помощью запятой, вы можете использовать формулу, основанную на функциях ПОДСТАВИТЬ и ОБРЕЗАТЬ. Вы можете использовать тот же подход для объединения значений в ячейках с любым разделителем, который вам нравится. В показанном примере формула в G5:
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")
Объяснение
Работая изнутри, формула сначала объединяет значения в 5 ячейках слева с помощью оператора конкатенации (&) и одного пробела между каждым значением:
B5&" "&C5&" "&D5&" "&E5&" "&F5
Эта часть формулы - досадно ручная. Чтобы ускорить процесс, скопируйте & "" & в буфер обмена перед тем, как начать. Затем следуйте этой схеме:
(щелкните) (вставьте) (щелкните) (вставьте) (щелкните) (вставьте)
пока не дойдете до последней ссылки на ячейку. На самом деле это прошло.
Результатом этой конкатенации (перед запуском TRIM и SUBSTITUTE) является строка, подобная этой:
"figs apples "
Затем мы использовали функцию TRIM, чтобы «нормализовать» все интервалы. TRIM автоматически удаляет пробелы в начале и конце данной строки и оставляет только один пробел между всеми словами внутри строки. Это устраняет лишние пробелы, вызванные пустыми ячейками.
"figs apples"
Наконец, SUBSTITUTE используется для замены каждого пробела ("") запятой и пробелом (","), возвращая такой текст:
"figs, apples"
Объединение ячеек с другими разделителями
Чтобы объединить ячейки с другим разделителем (разделителем), просто адаптируйте аргумент «новый_текст» внутри ЗАМЕНА. Например, чтобы соединить ячейки с косой чертой, используйте:
=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")
Результат будет выглядеть так:
limes/apricots/apricots/limes/figs
TEXTJOIN, функция
Функция TEXTJOIN - это новая функция, доступная в Office 365 и Excel 2019. TEXTJOIN позволяет объединить диапазон ячеек с помощью разделителя. С TEXTJOIN приведенный выше пример будет выглядеть так:
=TEXTJOIN(", ",TRUE,B5:F5)
Макрос
У Джона Акампора из ExcelCampus есть отличный макрос для объединения ряда ячеек.
