3 способа склеить текст из нескольких ячеек
Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»
Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:
Нюанс: не забудьте о пробелах между словами - их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст.
Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом - функция СЦЕП (CONCAT). Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны - текст из всех ячеек всех диапазонов будет объединен в одно целое:
Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN), появившуюся начиная с Excel 2016. У нее следующий синтаксис:
=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 ... )
где
- Разделитель - символ, который будет вставлен между фрагментами
- Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
- Диапазон 1, 2, 3 ... - диапазоны ячеек, содержимое которых хотим склеить
Способ 2. Символ для склеивания текста (&)
Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.
Для суммирования содержимого нескольких ячеек используют знак плюс "+", а для склеивания содержимого ячеек используют знак "&" (расположен на большинстве клавиатур на цифре "7"). При его использовании необходимо помнить, что:
- Этот символ надо ставить в каждой точке соединения, т.е. на всех "стыках" текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
- Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel - в этом же случае их надо ставить вручную.
Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:
Если сочетать это с функцией извлечения из текста первых букв - ЛЕВСИМВ (LEFT), то можно получить фамилию с инициалами одной формулой:
Способ 3. Макрос для объединения ячеек без потери текста.
Имеем текст в нескольких ячейках и желание - объединить эти ячейки в одну, слив туда же их текст. Проблема в одном - кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность - в живых остается только текст из верхней левой ячейки.
Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик - Visual Basic (Developer - Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню Insert - Module) и скопируем туда текст такого простого макроса:
Sub MergeToOneCell()
Const sDELIM As String = " " 'символ-разделитель
Dim rCell As Range
Dim sMergeStr As String
If TypeName(Selection) <> "Range" Then Exit Sub 'если выделены не ячейки - выходим
With Selection
For Each rCell In .Cells
sMergeStr = sMergeStr & sDELIM & rCell.Text 'собираем текст из ячеек
Next rCell
Application.DisplayAlerts = False 'отключаем стандартное предупреждение о потере текста
.Merge Across:=False 'объединяем ячейки
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) 'добавляем к объед.ячейке суммарный текст
End With
End Sub
Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.
Ссылки по теме
- Делим текст на куски
- Объединение нескольких ячеек в одну с сохранением текста с помощью надстройки PLEX
- Что такое макросы, как их использовать, куда вставлять код макроса на VBA
Этот наглядный пример как объединить ячейки избавил меня тот нудной работы на целый день 31 декабря!
Жаль что только с 2016 Excel
Кучу времени сэкономлю при заполнении путевого листа
Например:
=СЦЕПИТЬ("Уважаемый, ";B7;", на ваше письмо № ";B5;" от ";ТЕКСТ(B6;"ДД.ММ.ГГГГ");" сообщаем...")
Подскажите, пожалуйста, как в макросе прописать, чтобы при склеивании текста на определенном этапе происходил переход на новую строку? Т.е. часть склеиваемого текста должна начинаться с новой строки. Заранее благодарю..
Chr(13) - это символ перехода на новую строку. Вместо этой функции можно также писать vbNewLine.
ошибка #ИМЯ?
в чем проблема?
Если вам надо в ячейке, то нужно заменить функцию VBA Chr на ее аналог из Мастера функций - СИМВОЛ(13) и все пробелы убрать.
В таком варианте не работает. #NAME? и подчёркивает chr
Символ(10) переност строки
Заранее спасибо.
Николай, огромное спасибо за эту функцию.
Но она к сожалению игнорирует скрытые и сгруппированные ячейки.
А есть ли безмакросный (ну или макросный) путь автоматизировать следующие объединения.
Есть лист с контактными данными (email) ряда контрагентов, причем в одном столбце прописаны коды клиента, а в другом по одному Email. Следовательно, может несколько раз подряд идти один и тот же код клиента, а почтовые адреса разные.
Пример:
На другом листе есть список клиентов с кодами и туда надо по этим кодам собрать все адреса, чтобы можно было скопировать одну ячейку и вставить в Outlook, либо применить макрос для отправки.
Пример нужного результата:
Буду крайне признателен за мысли и советы!
Помогите советом! Необходимо, чтобы ФИО склеивалось из трех колонок (фамилия, имя, отчество), но на другом листе и при выборе через выпадающий список (выбор по фамилии).
Это возможно осуществить?
Например,
А В С
Иванов Иван Иванович
Петров Петр Васильевич
Васильев Николай Владимирович
На другом листе в одной ячейке необходимо через выпадающий список получить "Петров Петр Васильевич"
А как сделать так, чтобы не было объединения ячеек, чтобы макрос записывал все в верхнюю левую ячейку выделенного диапазона, а остальные ячейки делал пустыми.
Если удалить строку
Да вроде просто: У меня получилось.
Может коряво выглядит, но работает )
Sub MergeToOneCell()
Const sDELIM As String = " " 'символ-разделитель
Dim rCell As Range
Dim sMergeStr As String
If TypeName(Selection) <> "Range" Then Exit Sub 'если выделены не ячейки - выходим
With Selection
For Each rCell In .Cells
sMergeStr = sMergeStr & sDELIM & rCell.Text 'собираем текст из ячеек
rCell="" '(добавляем строку)
Next rCell
Application.DisplayAlerts = False 'отключаем стандартное предупреждение о потере текста
'.Merge Across:=False 'объединяем ячейки (комментим эту строку)
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) 'добавляем к объед.ячейке суммарный текст
End With
End Sub
Можно ли сцепить ячейки и текст из вкладки. нужно проставить нумерацию так, что бы начало нумерации было из текста вкладки (листа), а дальше сцеплялось с уже имеющаяся нумерация на листе
=ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1)))
где
А2 - любая ячейка на листе
берется текст из A1 сцепляется с A2, записывается текстом в A3 (в этом помогает скрипт сверху)
берется текст из B1 сцепляется с B2, записывается текстом в B3
берется текст из ... сцепляется с..., записывается текстом в ...
берется текст из N1 сцепляется с N2, записывается текстом в N3
и так 100 строк например. скрипт позволяет один раз сцепить, а хотелось бы чтоб диапазон сцепился и сохранился ТЕКСТОМ!
Спасибо.
Подскажите, пожалуйста, как можно сделать следующее:
имеются ячейки, в которых есть текст на английском и русском. Пример:
Toyota Camry Ford
Тойота Камри Форд и так далее...
Нужно в отдельную ячейку вытащить только английский текст: "Toyota Camry Ford", а не "Toyota Тойота Camry Камри Ford Форд". С функцией CONCATENATE у меня проблем нет. Не знаю лишь, каким образом (скорее всего макросом) можно вытащить только английский текст. Хочу сказать, что возможности разбить текст на разные отдельные нет. Могу лишь при необходимости внутри ячейки разбить английский и русский текст каким-нибудь знаком, например "/".
Очень надеюсь на Вашу помощь! Обычно всегда находил ответы на вопросы на Вашем сайте, а в этот раз прямо-таки прецендент...
У меня есть таблица вида
2. Австрия — Австрийская Республика
3. Азербайджан — Азербайджанская Республика
Нужно сделать в строчку:
Австалия, Австрия, Азербайджан
PS: В начале статьи есть фраза:
"Нюанс: не забудьте о пробелах между словами - их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. "
Возможно, я неверно понял смысл, но мне кажется, что "ибо текст" надо заключать в кавычки, а не в скобки. В формулах всё правильно - в кавычках.
=15&"%"&СИМВОЛ(10)&55&" руб"
Где: СИМВОЛ(10) – символ переноса строки
Попробуйте Файл - Учетная запись - Обновить.
Подскажите, пожалуйста, как можно изменить Function MultiCat (описана выше), чтобы она не учитывала пустые ячейки, т.е. нужен результат как делает функция ОБЪЕДИНИТЬ для Excel 2016.
У меня есть данные, которые случайным образом появляются, т.е. могут быть в первой ячейке, а могут начинаться только в 5 или 7 ячейке. Разделитель использую ; и тогда результат получается в виде:
;;Андрей;;;;Иван;;;Дмитрий;;;Сергей;;;
Как можно убрать из сцепки пустые ячейки (а вместе с ними исчезнут избыточные ; ) ?
Если серьезно, то в последнем обновлении моей надстройки PLEX я такую штуку добавил - TextJoin называется.
вроде все правильно написал
=ОБЪЕДИНИТЬ(";";ИСТИНА;EY6:IV6)
Подскажите, пожалуйста, как можно вывести список значений в одну ячейку по критерию истина.
т.е. в файле выведен список значений, для каждого добавил "флажок". Если значение принимает истину, то отображается в отдельной ячейки и так списком через ;.
За подсказку буду благодарен!!!!
например: дано текст из ячеек H6 H8 сцепить в B10 при условии наличия текста ФИО в B2
очень буду признателен за помощь!
P/S умные люди подсказали вот так:
=ЕСЛИ(B6="";"";СЦЕПИТЬ($H$1;"/";$H$2))
1) вводим в ячейке =СЦЕПИТЬ(
2) выделяем диапазон, напр. A1:C1
3) добавляем в кавычках разделитель &","
в итоге формула будет выглядеть примерно так:
=СЦЕПИТЬ(A1:C1&",")
4) выделяем всё содержимое, то что внутри скобок A1:C1&","
5) жмём F9
наши значения склеились с разделителем и обрамились фигурными скобками
6) удаляем фигурные скобки из формулы
7) Enter(ввод)
вуаля, профит )
В частости сейчас записана такая формула: =ЕСЛИ(N14>0;(СЦЕПИТЬ(ОКРУГЛ(N14;0);"/";ОКРУГЛ(O14;0);"/";(ОКРУГЛ(P14;0))));ЕСЛИ(O14>0;(СЦЕПИТЬ(ОКРУГЛ(O14;0);"/";ОКРУГЛ(P14;0)));ОКРУГЛ(P14;1))) - значение "-" отражаются, а с "+" не знаю что придумать. Пробовал через "формат ячеек", но не работало. Спаибо заранее.
В первом способе написано, что функция объединить есть начиная с 2016-го экселя
На сайте Майкрософта написано:
"Функция ОБЪЕДИНИТЬ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac"