Excel склеить текст из ячеек

Объединить текст в Excel

Задача, о которой пойдет речь в текущей статье, является очень специфической. Далеко не каждый сходу скажет, где ее можно выполнять. Но это только на первый взгляд. Я хочу рассказать о том, как можно склеить, сцепить или объединить текст, находящийся в разных ячейках. Дело в том, что сцепить, склеить или объединить в данном контексте являются синонимами. Именно поэтому лично для меня удобнее и привычней использовать понятие «объединить».

Зачем нужно объединять текст ячеек в excel

Самым простым и очевидным ответом станет буквальность – для того, чтобы поместить текст из нескольких ячеек в одну. А вот как этим пользоваться это уже другой вопрос. Например, можно записывать ФИО человека из трех разных ячеек в одну. Зачем спросите вы, я же скажу так «мне эту задачу ставили раз нцать, особенно бухгалтера». Каждый раз были нюансы. Но во всех случая, список сотрудников построен, так что фамилия имя и отчества хранились в разных ячейках. Кроме того, задачи ставились так, что нужно объединить текст в ячейку по полному содержимому и по инициалам. Иванов Иван Иванович и Иванов И.И., а еще было так И.И. Иванов. Именно поэтому я вам расскажу, как я решал поставленные задачи.

Использование функции СЦЕПИТЬ

Наиболее понятным способом объединения текста в excel является использование функции СЦЕПИТЬ. Функция может принимать 255 параметров склеиваемых текстовых строк. На рисунке видно как она работает.

Объединение текста с использование разделителя

Полученный результат объединения нас не устроит, ведь мы привыкли видеть пробелы между словами, поэтому формулу нужно дополнить к такому виду

=СЦЕПИТЬ(C8;» «;D8;» «;E8)

В этом случае мы увидим все красиво. Вполне возможно такой вариант устроит большинство, но всегда есть неожиданное «НО». Нам нужно записать ФИО в формате «Иванов И.И.». В этом случае формула будет выглядеть немного сложнее, ко всему к ней еще добавится и использование функции ЛЕВСИМВ.

=СЦЕПИТЬ(C8;» «;ЛЕВСИМВ(D8);».»;ЛЕВСИМВ(E8);».»)

Результатом такого склеивания будет таблица изображенная на следующем рисунке.

Объединение ячеек с использованием дополнительной функции ЛЕВСИМВ

Использование оператора склеивания — &

Для тех, кто заинтересован в более коротком варианте формулы существует оператор «склеивания» — «&». Некоторые считают, что его использование удобнее. Но я думаю, что принципиальной разницы, с точки зрения результата не будет. Первая формула примет следующий вид «=C8&» «&D8&» «&E8» и даст такой же результат объединения текста как и то, что мы видим на первом рисунке

Я думаю, вы уже догадались, что для того, чтобы получить результат, показанный на втором рисунке, нужно опять воспользоваться функцией ЛЕВСИМВ. Тогда наша формула примет следующий вид «=C8&» «&ЛЕВСИМВ(D8)&».»&ЛЕВСИМВ(E8)&».»» Использования символа склеивания удобней еще и тем, что позволяет склеивать более 255 строк.

Я думаю, для большинства не составит труда использовать любой из вариантов склеивания текста в excel. Я так говорю, потому что есть еще один способ склеивания, но ним будут пользоваться только продвинутые пользователи. Это способ склеивания с помощью VBA.

VBA – сложный способ

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

Объединение текста и склеивание ячеек с помощью VBA

Довольно простая функция, в которой разберется любой программист. Можно просто объединить текст в какую-то ячейку или вообще склеить ячейки. В любом случае, VBA в умелых руках да при знающей голове способен удивить любого пользователя. Поэтому пользуйтесь и стремитесь.

Сводные таблицы Excel

Чтобы правльно склеить или соединить разный и не разный текст из нескольких ячеек таблицы Excel, существует несколько способов.

Способ 1. Функция СЦЕПИТЬ

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

Способ 1. Функция СЦЕПИТЬ

Кстати, не забудьте о пробелах между словами – их надо прописывать как отдельные аргументы и заключать в скобки, ибо пробел тоже текст.

Способ 2. Символ для склеивания текста (&)

Для суммирования содержимого нескольких ячеек используют знак +, а для склеивания содержимого ячеек используют знак & (расположен на большинстве клавиатур на цифре 7).

Способ 2. Символ для склеивания текста

При его использовании необходимо помнить, что:

 Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк, так же как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8).
 Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки, как и в предыдущем способе.

Способ 3. Макрос для объединения ячеек без потери текста

Начинающие пользователи часто пытаются использовать для объединения ячеек с текстом кнопку Объединить и поместить в центре (Merge and Center) с вкладки Главная (Home).

Проблема в одном – объединять ячейки эта функция умеет

Проблема в одном – объединять ячейки эта функция умеет, а вот с текстом сложность – в результирующей объединенной ячейке остается текст только из верхней левой ячейки, а остальной текст мы теряем. Microsoft Excel об этом, кстати, честно предупреждает при попытке выполнения этой операции.

Microsoft Excel честно предупреждает при попытке выполнения операции

Сделать свой вариант этой функции с сохранением (точнее – слиянием) текста из всех объединяемых ячеек можно с помощью небольшого макроса. Откройте редактор 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, то Excel объединит выделенные ячейки в одну, слив туда же и текст из всех ячеек через пробелы. Вместо пробела, конечно же, можно использовать другой разделитель – введите его во второй строке макроса в переменную sDELIM.

Excel: склеиваем текст из разных ячеек

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

Простое решение: использовать функцию СЦЕПИТЬ

В текстовых формулах есть стандартная функция СЦЕПИТЬ, которая может объединить текст, числа или ссылки объёмом до 255 строк из разных ячеек в одну:

Важно: символы-разделители должны быть обозначены как отдельные аргументы. В приведенном примере мы использовали пробелы; аналогично аргументами могут выступать запятые, точки и прочие символы, заключенные в кавычки. Разделители прописываются вручную.

Красивое решение: использовать амперсанд

Альтернативой для функции СЦЕПИТЬ может выступать амперсанд – знак «&» (в большинстве случаев расположенный на цифре «7»).

• Амперсанд ставится на «стыках ячеек», то есть между их содержимым (по аналогии с тем, как вы используете знак «+» при суммировании чисел: 7+5+11+3).
• Добавлять пробелы, запятые или любые другие аргументы (даже целые слова) между ячейками придется вручную. Аргументы также нужно заключать в кавычки.

Кстати, в случае необходимости полные имена вы легко можете сократить до инициалов посредством функции ЛЕВСИМВ: выделяете нужную ячейку и оставляете первый символ; не забудьте указать подходящий разделитель!

Непривычное решение: макрос MergeToOneCell

Объединить ячейки, конечно, может кнопка Объединить и поместить в центре . Но здесь возникает проблема:

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

• вызываем редактор 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

Теперь выделяйте ячейки, содержимое которых нужно объединить, и запускайте макрос MergeToOneCell путем нажатия горячих клавиш ALT+F8 . Так вы «сольёте» содержимое разных ячеек в одну (текст будет разделен пробелами):

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) и скопируем туда текст такого простого макроса:

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Как в Excel объединить текст из двух и нескольких ячеек в одну

Объединение текста нескольких ячеек в одну – базовая функция при работе с табличными данными, с ее п.

Объединение текста нескольких ячеек в одну – базовая функция при работе с табличными данными, с ее помощью содержание произвольного количества полей можно сцеплять нужным образом и размещать в свободных ячейках. Она используется при составлении пофамильных списков сотрудников, отчетов различных направлений и других операциях с большими массивами информации. Для объединения ячеек есть два способа: с потерей данных и без потери. В первом случае в объединенной ячейке остается только одно значение, во втором – сохраняется весь текст. Второй способ предполагает как минимум четыре варианта объединения: с использованием двух функций («Сцеп-сцепить» и «Объединить»), при помощи символа амперсанд и с применением макроса. В статье подробно разберемся, как в Excel объединить текст нескольких ячеек в одну.

С помощью функции

Excel предусматривает несколько функций для объединения ячеек. Для начала, расскажем о самой простой и «теряющей» информацию. Если интересует, как в Экселе соединить текст из двух ячеек в одну, оставив только одно значение – то вам понадобится функция «объединить» в разделе «Формат ячейки».

1. Выделите мышкой ячейки, которые нужно объединить.
2. По образовавшемуся полю щелкните правой кнопкой мыши.
3. Выберите пункт «Формат ячеек».
4. В появившемся окне зайдите в раздел «Выравнивание».
5. Отметьте галочкой пункт «Объединить ячейки» и нажмите «Ок». Выделенные поля объединяться в одну большую с сохранением содержания первой ячейки.

Также эта функция есть в графе «Главная» в разделе «Выравнивание» в верхней части экрана.

С помощью функции «Сцеп-сцепить»

В ранних версиях Excel для объединения текста ячеек применялась функция «Сцепить», в новых вариантах программы ее заменили функцией «Сцеп». Для ее использования:

1. Щелкните по пустой ячейке в Excel.
2. Поставьте знак «=» и большими буквами введите «Сцепить» либо «Сцеп».
3. В скобках после формулы кликните по ячейке, которая должна быть первой в объединенной ячейке, либо введите ее адрес вручную (например, A1). Затем поставьте точку с запятой, после щелкните или напишите вторую ячейку. Затем поставьте точку с запятой и отметьте третье поле. После перечисления адресов нажмите «Enter». В поле, где вбивали формулу, появится объединенный текст. Он появится без пробелов между текстовыми блоками. Чтобы этого избежать, отбейте пробел в каждой отдельно взятой ячейке после находящегося текста, либо после адреса ячейки в формуле добавьте пробел вручную, написав « » (пробел в кавычках). Пробел в формуле также отделяется от остальных элементов точкой с запятой.

Как в Экселе соединить текст из двух ячеек в одну с помощью функции «Объединить»

«Объединить» — усовершенствованная вариация функции «Сцеп». Ее отличие в том, что параметры вводятся единожды на этапе написания функции. В частности, разделитель придется указать один раз для содержимого всех ячеек. Для того чтобы воспользоваться функцией «Объединить» нужно выполнить действия:

1. В пустой ячейке поставьте знак «=» и напишите большими буквами «Объединить».
2. В скобках после формулы укажите разделитель в кавычках (« » ,«,»,« и »), затем поставьте точку с запятой. Второй параметр формулы – это игнорирование пустых полей. Если они не должны учитываться в формуле, напишите «1», в ряде версий этот параметр обозначается словом «Истина» или «Ложь». Затем перечислите через запятую ячейки, которые нужно объединить, и нажмите «Enter».

В итоге формула должна иметь вид: «=ОБЪЕДИНИТЬ(« »;ИСТИНА; «A1»; «A2»; «A3»).

Если в A1 – «Иванов», в А2 – «Иван», в А3 – «Иванович», то после нажатия «Enter» в пустой ячейке появится «Иванов Иван Иванович».

С помощью символа «&»

Четвертый способ объединить ячейки – использовать амперсанд ( «&»). Для этого:

1. В пустой ячейке наберите «=».
2. Напишите нужную ячейку, например, А1, затем поставьте знак «&», потом в кавычках введите разделитель. Это может быть пробел или запятая, он также помещается в кавычки, потом введите вторую ячейку, которую нужно объединить. Если их больше двух, пробел выставите после каждой нового поля. Формула должна выглядеть так: «=А1&« »&А2& « »&А3». После нажатия «Enter» содержимое ячеек объединится.

С помощью макроса

Макрос в Excel – это сервис для автоматизации повторяющихся действий, он представляет собой записанную последовательность примененных функций и щелчков мышью для последующего воспроизводства. Используется, если нужно регулярно выполнять типовые задачи, в которых меняются данные, но не структура. Например, макросы используются при составлении ежемесячных отчетов о заработной плате, в которых меняются только цифры, но не метод расчета. Тогда макрос избавит пользователя от необходимости регулярно вручную настраивать и использовать формулы.

Чтобы записать макрос:

1. В разделе «Разработчик» в графе «Код» нажмите на клавишу «Записать макрос».
2. В появившемся окне выберите имя макроса, чтобы потом было удобнее его искать, присвойте ему сочетание клавиш и место хранения, добавьте описание.
3. Нажмите «Ок», затем точно и без ошибок (потому что макрос записывает и ошибки тоже) проделайте алгоритм.
4. Вернитесь во вкладку «Разработчик» и «Код» и нажмите «Остановить запись».

Макрос записывается в виде кода Visual Basic для приложений на одноименном языке программирования, который доступен для редактирования при нажатии комбинации ALT + F11. При ручном редактировании становится доступным правка записанного макроса, однако это требует дополнительных знаний и недоступно для рядового пользователя. Также макросы способны работать в нескольких продуктах Microsoft Office одновременно. Выполняется макрос нажатием присвоенной комбинации либо через окно «Макрос», которое вызывается комбинацией ALT + F8.