Объединение с разделителями с помощью TEXTJOIN

На прошлом уроке Вы узнали, что CONCAT может объединять диапазон, но не умеет вставлять разделитель между отдельными значениями.

Представьте, что у Вас есть столбец с названиями городов, и Вы хотите получить из них один список, разделённый запятыми: Paris, Tokyo, Cairo. Чтобы сделать это с помощью CONCAT, пришлось бы вводить каждую запятую вручную.

TEXTJOIN элегантно решает эту задачу: Вы указываете один разделитель, и функция автоматически помещает его между всеми значениями.

У TEXTJOIN есть три части:

• разделитель — текст, размещаемый между значениями, например ", "
• пропуск пустых ячеек — значение TRUE для пропуска пустых ячеек и FALSE для их сохранения
• текст1, текст2, ... — ячейки или диапазоны, которые нужно объединить

Общий вид: =TEXTJOIN(delimiter, ignore_empty, text1, ...). Разделитель используется только между элементами, но не в самом начале или конце.

Предположим, что в диапазоне A2:A4 находятся значения Paris, Tokyo и Cairo.

Приведённая ниже формула объединяет их, помещая между каждым значением запятую и пробел. В результате получается Paris, Tokyo, Cairo.

Обратите внимание: перед Paris и после Cairo нет запятых. TEXTJOIN вставляет разделитель только между значениями.

Разделителем может быть любой текст. Вот несколько распространённых вариантов:

• ", " для удобного чтения списка
• " - " для пути, части которого разделены тире
• "; " для списков с разделением точкой с запятой
• " " для одного пробела, например при создании полного имени

Здесь мы объединяем части имени, используя один пробел.

Аргумент пропуска пустых ячеек очень полезен. Установите для него значение TRUE, и все пустые ячейки в диапазоне будут пропущены, поэтому двойных разделителей не появится.

Предположим, что в диапазоне A2:A4 находятся Paris, пустая ячейка и Cairo. Если пропуск пустых ячеек включён, получится Paris, Cairo, а не Paris, , Cairo.

Иногда пустые позиции действительно нужно сохранить, например чтобы выровнять расположение данных.

Установите для аргумента пропуска пустых ячеек значение FALSE, и пустые ячейки всё равно создадут разделитель. Если в A2:A4 находятся Paris, пустая ячейка и Cairo, результатом будет Paris, , Cairo.

Выбирайте TRUE для аккуратных списков и FALSE, когда расположение позиций имеет значение.

Предположим, что в строке хранятся данные заказа: ID в A2, товар в B2, количество в C2, а цена в D2.

Чтобы экспортировать их в виде строки с разделением запятыми, объедините весь диапазон, указав запятую в качестве разделителя.

Результат может выглядеть так: 1001,Notebook,3,4.99 — его можно сразу вставить в файл CSV.

TEXTJOIN может принимать несколько диапазонов и отдельные ячейки в качестве отдельных аргументов, используя один и тот же разделитель.

Например, объедините два отдельных столбца с метками в один список:

Все значения из обоих диапазонов объединяются по порядку, а между ними помещается выбранный Вами разделитель.

Можно разместить значения на отдельных строках внутри одной ячейки, используя символ переноса строки в качестве разделителя.

В Excel этот символ задаётся как CHAR(10). В Google Sheets также можно использовать CHAR(10).

После ввода формулы включите для ячейки перенос текста, чтобы переносы строк стали видимыми.

У Вас есть столбец с адресами электронной почты в диапазоне A2:A10, и Вы хотите получить из них одну строку с разделением точками с запятой, чтобы вставить её в поле «Кому» электронного письма.

Используйте в качестве разделителя точку с запятой и пробел, а пустые ячейки пропускайте, чтобы список оставался аккуратным.

В результате получится одна готовая к вставке строка получателей, которая будет обновляться при добавлении адресов.

Вот краткое руководство по выбору:

• Используйте CONCAT или &, когда объединяете несколько конкретных элементов с разными разделителями или без них.
• Используйте TEXTJOIN, когда объединяете диапазон и хотите использовать один и тот же разделитель между всеми значениями, особенно если нужно пропускать пустые ячейки.

TEXTJOIN особенно удобен для списков, строк CSV и строк меток, где вводить каждый разделитель вручную было бы утомительно.

Проверьте свои знания о TEXTJOIN.

Теперь Вы умеете создавать списки с разделителями:

• =TEXTJOIN(delimiter, ignore_empty, text1, ...)
• Разделитель помещается между значениями, но не в начале и не в конце.
• ignore_empty = TRUE пропускает пустые ячейки, создавая аккуратные списки.
• Функция принимает диапазоны, поэтому одна формула обрабатывает целый столбец.
• Используйте CHAR(10) для вставки переносов строк внутри ячейки.

Далее Вы научитесь приводить регистр текста к единому виду с помощью UPPER, LOWER и PROPER.