Как склеить текст из ячеек в Google таблице, функция CONCATENATE

Одна из самых распространенных задач — соединить текст или числа из разных ячеек, либо столбцов в таблице гугл или эксель в один логичный текст. Будь то объединение адреса и города из разных ячеек или составить ФИО из таблицы с данными. Давайте рассмотрим варианты, которые помогут нам успешно реализовать эту задачу.

В текстовом репозитории Google таблиц есть функция CONCATENATE (СЦЕПИТЬ), которая соединяет содержимое нескольких ячеек в одну структуру, позволяя комбинировать данные в свободной форме.

Синтаксис:

 =CONCATENATE(text1; [text2]; …)

 =СЦЕПИТЬ(текст1; [текст2]; …)

• Text1 (Текст1) — значение, которое нужно объединять с другим значением. Значение может быть: текстом, числом, ссылкой на ячейку, функцией.
• [Text2 (Текст2)] — значение, которое нужно объединить с Text1 (Текст1) переменной. Всего в одной функции можно использовать до 254 значения, суммарное число символов не может превышать 8192 символа.

Рассмотрим простой пример работы функции CONCATENATE (СЦЕПИТЬ) и соединим данные из строк и столбцов в одну структуру:

Вы обратили внимание, что данные свелись в одну структуру, но логики в ней нет. Не хватает пробелов между значениями ячеек. И это, пожалуй, самый большой минус данной функции. Чтобы, все же, появились пробелы — их нужно добавить в формулу, либо организовать дополнительную ячейку с пробелом, на которую мы будем ссылаться каждый раз, когда будем «склеивать» содержимое ячеек.

Оба эти варианта рабочие и имеют место быть, когда данных не так много. Чтобы добавить пробел в формулу, его записывают так: « » (пробел в кавычках); либо в ячейке проставим пробел, из которой он каждый раз будет изыматься формулой.

Не все знают, но чтобы «сцепить» содержимое ячеек можно воспользоваться символом — «&» (Амперсанд). Это та же сцепка, какая синтаксически записывается в формуле с пробелами в кавычках, только уже без самого названия:

 =(text1&" "&[text2]&" "&[text3])

 =(A2&$E$35&B2&$E$35&C2)

Рассмотрим реальный пример серьезного уместного применения функции CONCATENATE (СЦЕПИТЬ) через Амперсанды. В данном случае, применяется сцепка со специальными символами «| |» (требования Яндекс Директ), для правильного формирования строчного списка Заголовков фида для рекламы в Яндекс Директ. Так же, формула содержит функцию IF (ЕСЛИ), с которой мы познакомились в прошлой статье. Формула имеет следующий вид:

 =IF(I3="";"";'Настройки №2'!$B$2&"||"&'Настройки №2'!$B$3&"||"&'Настройки №2'!$B$4&"||"&'Настройки №2'!$B$5&"||"&'Настройки №2'!$B$6&"||"&'Настройки №2'!$B$7&"||"&'Настройки №2'!$B$8&"||"&'Настройки №2'!$B$9)

Многомерная сложная формула сцепляет содержимое в ячейках из другого листа, отображая список заголовков через заданные разделители. Потом мы этот готовый шаблон Яндекс Директ Google (Excel) импортируем в Директ Коммандер.

В свежих обновлениях добавилась новая функция CONCAT (СЦЕП). Она так же объединяет ячейки с данными в одну структуру, только в отличие от CONCATENATE (СЦЕПИТЬ) — состоит только из 2 обязательных аргументов, но может вмещать в себя в 4 раза больше символов. Чтобы в полной мере освоить данную функцию, ее применяют вместе с амперсандом.

 =CONCAT(value_1; value_2)

 =СЦЕП(значение_1; значение_2)

Value_1 (Значение_1) — значение, которое нужно объединять с другим значением. Значение может быть: текстом, числом, ссылкой на ячейку, функцией.

Value_2 (Значение_2) — значение, которое нужно объединить с Value_1 (Значение_1) переменной. Всего в одной функции можно использовать до 253 аргументов, суммарное число символов не может превышать 32767 символа.

Для массового объединения значений в ячейках с 2016 года (Excel), с 2017 года (Google таблицы) появилась универсальная функция TEXTJOIN (ОБЪЕДИНИТЬ). Функция сводит в одну ячейку текст, число, ссылки на другие ячейки или информацию со всего массива / нескольких массивов данных. Суммарное число символов в одной ячейке не может превышать 32767 символов. Синтаксис:

 =TEXTJOIN(separator; exclude_blanks; text1; [text2]; …)

 =ОБЪЕДИНИТЬ(разделитель; исключить_пустые; текст1; [текст2]; …)

• Separator (Разделитель) — указанный символ, который будет разделять объединенные значения (точка, запятая, косая черта, и т.д.).
• Exclude_blanks (Исключить_пустые) — аргумент отвечает за то, нужно ли игнорировать пустые ячейки TRUE (ИСТИНА) или  FALSE (ЛОЖЬ).
• Text1 (Текст1) — текст, ссылка на ячейку, число или диапазон, к которому будет применен процесс объединения — обязательный аргумент.
• [Text2 (Текст2)] — текст, ссылка на ячейку, число или диапазон, который будет объединяться с Text1 (Текст1).

Рассмотрим сложный вариант работы данной формулы при решении реальной задачи объединения текста через запятую из двух столбцов и отображении результата в одной ячейке.

У нас есть минус-фразы для рекламной кампании Google Ads и Яндекс Директ. Они записаны в 2 столбика: 1 — тематические минус-фразы; 2 — универсальные. Нам их нужно объединить в одну ячейку, чтобы каждая фраза шла через запятую. В этом нам поможет функция TEXTJOIN (ОБЪЕДИНИТЬ). Помимо основной задачи, у нас есть еще ссылка на условие через известную уже нам функцию IF (ЕСЛИ).

 =IF(I3="";"";TEXTJOIN(", ";TRUE;'Ключевики'!$E$2:E;'Ключевики'!$F$2:F))

Функция объединяет в одну ячейку столбец E и столбец F, игнорируя заглавия колонок, проставляет через запятую минус-фразы и отображает в выбранной ячейке (в другой, итоговой вкладке) этот результат, опираясь на заданное условие IF (ЕСЛИ).

Поделитесь в комментариях, получилось ли у вас воспользоваться функциями объединения и с какими проблемами вы столкнулись, когда решали поставленные задачи?