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 объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

 


Lector
28.10.2012 09:57:13
СПАСИБО Николай!!!
Этот наглядный пример как объединить ячейки избавил меня тот нудной работы на целый день 31 декабря!
17.04.2017 12:07:39
ОБЪЕДИНИТЬ – это вещь! Спасибо!
Жаль что только с 2016 Excel
Денис
28.10.2012 09:57:35
Огромнейше спасибо!
Кучу времени сэкономлю при заполнении путевого листа :))) УРА!.
Юлия
28.10.2012 09:58:04
Уточните, пожалуйста, в способе 2 и 1, можно ли в ячейке, где объединенный текст из нескольких, задать формат отображаемого текста, например, выделить жирным или подчеркнуть? Задать формат для всей ячейке - понятно, но сделать два слова из 10, например, подчеркнутыми.
28.10.2012 10:12:04
Насколько я знаю, нет. Такое только макросом.
Таня
28.10.2012 09:58:47
Можно ли формулами объединить в одну ячейку текст и дату?
Юлия
28.10.2012 10:08:30
Можно. Но надо задать формат даты для ячейки, в которой она указана.
Например:
=СЦЕПИТЬ("Уважаемый, ";B7;", на ваше письмо № ";B5;" от ";ТЕКСТ(B6;"ДД.ММ.ГГГГ");" сообщаем...")
Андрей
28.10.2012 10:11:22
Доброе время суток!
Подскажите, пожалуйста, как в макросе прописать, чтобы при склеивании текста на определенном этапе происходил переход на новую строку? Т.е. часть склеиваемого текста должна начинаться с новой строки. Заранее благодарю..
28.10.2012 10:13:28
Можно использовать конструкцию
ваша строка & Chr(13) & продолжение строки

Chr(13) - это символ перехода на новую строку. Вместо этой функции можно также писать vbNewLine.
19.11.2013 12:08:04
Вставляю & Chr(13) &, не работает.
ошибка #ИМЯ?
в чем проблема?
20.11.2013 17:21:37
Пробелы до и после амперсандов (&) есть?
21.11.2013 18:54:16
пробовал по разному, не получается.
25.11.2013 23:50:52
вот моя формула:

=A1&" "&A2&" "&A3 & Сhr(13) & A4&" "&A5&" "&A6
 
в ошибке подчеркивает как раз "Chr"
26.11.2013 09:31:59
Я имел ввиду использовать эту конструкцию в коде VBA, а не в формуле в ячейке.
Если вам надо в ячейке, то нужно заменить функцию VBA Chr на ее аналог из Мастера функций - СИМВОЛ(13) и все пробелы убрать.
29.10.2015 14:08:29
=Q5&Chr(13)&R5
В таком варианте не работает. #NAME? и подчёркивает chr
25.12.2012 09:38:50
Испробовал третий способ - работает отлично. Но как быть, если целый список ФИО трех десятков народа разбиты по трем колонкам? Построчно (для одного человека) все нормально, а попытки слить одной командой "Выполнить" несколько человек соединяет не построчно, а всех в одну ячейку.
12.02.2013 08:42:21
Доброе время суток! Скажите, а можно ли представить данный макрос (способ 3) в виде пользовательской функции, т.е. функция должна возвращать "склеенный"  текст из выделенного диапазона ячеек. Стандартная СЦЕПИТЬ выглядит, как =СЦЕПИТЬ(текст1;текст2;..), а в данном случае, что-то вроде =MergeToOneCell(диапазон)?
Заранее спасибо.
13.02.2013 16:15:37
'склеивание текста из всех ячеек диапазона Rng с разделителем DELIM
Public Function MultiCat(ByRef Rng As Excel.Range, Optional ByVal DELIM As String = "") As String
    Dim rCell As Range
    For Each rCell In Rng
        MultiCat = MultiCat & DELIM & rCell.Text
    Next rCell
    MultiCat = Mid(MultiCat, Len(DELIM) + 1)
End Function
13.02.2013 16:31:00
Николай, огромное спасибо!. Формула в работе очень пригодится.  К сожалению сам не умею писать КОД.
05.06.2013 13:57:32
День добрый, а можно ли сцепить текст из разных ячеек в одну, но только чтобы из нужных ячеек брались только заглавные буквы?
28.07.2014 00:31:50
Добрый день!
А есть ли безмакросный (ну или макросный) путь автоматизировать следующие объединения.
Есть лист с контактными данными (email) ряда контрагентов, причем в одном столбце прописаны коды клиента, а в другом по одному Email. Следовательно, может несколько раз подряд идти один и тот же код клиента, а почтовые адреса разные.
Пример:
код клиентаemail
1a@mail.com
1b@mail.com
1c@mail.com
2fpg@mail.com

На другом листе есть список клиентов с кодами и туда надо по этим кодам собрать все адреса, чтобы можно было скопировать одну ячейку и вставить в Outlook, либо применить макрос для отправки.
Пример нужного результата:
код клиентаemail
11@mail.com; b@mail.com; c@mail.com
2fpg@mail.com

Буду крайне признателен за мысли и советы!
28.02.2015 19:21:10
Николай, доброго вечера!
Помогите советом! Необходимо, чтобы ФИО склеивалось из трех колонок (фамилия, имя, отчество), но на другом листе и при выборе через выпадающий список (выбор по фамилии).
Это возможно осуществить?

Например,
А В   С
Иванов Иван Иванович
Петров Петр Васильевич
Васильев Николай Владимирович

На другом листе в одной ячейке необходимо через выпадающий список получить "Петров Петр Васильевич"
09.07.2015 09:29:04
Спасибо огромное за макрос.
А как сделать так, чтобы не было объединения ячеек, чтобы макрос записывал все в верхнюю левую ячейку выделенного диапазона, а остальные ячейки делал пустыми.

Если удалить строку

.Merge Across:=False          'объединяем ячейки
то, объединения не происходит, но в нижних ячейках остается старый текст.
30.07.2015 11:02:53

 

Да вроде просто:  У меня получилось.
Может коряво выглядит, но работает )



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
02.09.2015 17:09:52
Подскажите, пожалуйста, если мне необходимо сцепить ячейки, некоторые из которых написаны Капс Локом. Нужно, чтобы в результате в объединенной ячейке те слова, которые были прописаны Капс Локом стали с заглавной, все остальные строчные. Грубо говоря было так Петров Петр Иванович ездит на CAMRY, а надо, чтоб стало так Петров Петр Иванович ездит на Camry. При чем в исходных ячейках необходимо оставить все заглавными буквами. Извините, если коряво объяснила. Очень срочно нужно!  
25.10.2015 20:43:55
Доброго времени суток. такой вопрос, а возможно сделать макрос, чтобы при выделении трех колонок с текстами он объединял ячейки с сохранением текста т.е, чтобы по одной строчке не выделять . Спасибо
11.01.2016 10:04:34
Добрый день.
Можно ли сцепить ячейки и текст из вкладки. нужно проставить нумерацию так, что бы начало нумерации было из текста вкладки (листа), а дальше сцеплялось с  уже имеющаяся нумерация на листе  
05.02.2016 00:58:46
Ахаха названия способов порадовали)
17.02.2016 12:06:36
Добрый день подскажите пожалуйста можно ли сцепить ячейку + наименование вкладки?
17.02.2016 19:32:53
Степан, если вам нужно имя листа, то его можно получить формулой:
=ПРАВСИМВ(ЯЧЕЙКА("имяфайла";A1);ДЛСТР(ЯЧЕЙКА("имяфайла";A1))-НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1)))
где
А2 - любая ячейка на листе
22.04.2016 04:50:24
Подскажите пожалуйста, как можно реализовать следующее.

берется текст из A1 сцепляется с A2, записывается текстом в A3 (в этом помогает скрипт сверху)
берется текст из B1 сцепляется с B2, записывается текстом в B3
берется текст из ... сцепляется с..., записывается текстом в ...
берется текст из N1 сцепляется с N2, записывается текстом в N3
и так 100 строк например. скрипт позволяет один раз сцепить, а хотелось бы чтоб диапазон сцепился и сохранился ТЕКСТОМ!

Спасибо.
11.06.2016 13:09:14
Добрый день.

Подскажите, пожалуйста, как можно сделать следующее:

имеются ячейки, в которых есть текст на английском и русском. Пример:

Toyota       Camry      Ford
Тойота      Камри      Форд      и так далее...

Нужно в отдельную ячейку вытащить только английский текст: "Toyota Camry Ford", а не "Toyota Тойота Camry Камри Ford Форд". С функцией CONCATENATE у меня проблем нет. Не знаю лишь, каким образом (скорее всего макросом) можно вытащить только английский текст. Хочу сказать, что возможности разбить текст на разные отдельные нет. Могу лишь при необходимости внутри ячейки разбить английский и русский текст каким-нибудь знаком, например "/".

Очень надеюсь на Вашу помощь! Обычно всегда находил ответы на вопросы на Вашем сайте, а в этот раз прямо-таки прецендент...
27.07.2016 20:24:21
Подскажите, пжл, как склеить чисто название стран без доп текста?
У меня есть таблица вида
1.  Австралия — Австралийский Союз
2.  Австрия — Австрийская Республика
3.  Азербайджан — Азербайджанская Республика
и так 195 стран, входящих в ООН (с вики)
Нужно сделать в строчку:

Австалия, Австрия, Азербайджан
12.04.2017 20:21:28
Надо же, статья аж с 2012 года, а я и не видел и не знал о наличии функций СЦЕП и ОБЪЕДИНИТЬ! Спасибо!
PS: В начале статьи есть фраза:
"Нюанс: не забудьте о пробелах между словами - их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. "
Возможно, я неверно понял смысл, но мне кажется, что "ибо текст" надо заключать в кавычки, а не в скобки. В формулах всё правильно - в кавычках.
17.04.2017 12:02:42
Мой любимый трюк перенос строки в ячейке с помощью формулы – полезный способ визуализации при построении отчетов:
=15&"%"&СИМВОЛ(10)&55&" руб"
Где: СИМВОЛ(10) – символ переноса строки
22.04.2017 12:54:35
Есть ли возможность сцепить все варианты значений 2-х, 3-х, n- диапазонов разных размеров?
28.04.2017 21:34:26
Здравствуйте. Установили сегодня Excel 2016, но почему я не нашел в нем функций "объединить" и "сцеп". Не подскажите в чем может быть причина?