Загальна формула
=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"
Приєднання комірок до інших роздільників
Щоб об’єднати комірки з іншим роздільником (роздільником), просто адаптуйте аргумент "new_text" всередині SUBSTITUTE. Наприклад, щоб об’єднати комірки косою рискою вперед, використовуйте:
=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 має класний макрос для об'єднання ряду комірок.
