Как сцепить данные из нескольких ячеек в одну

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

Данные из нескольких ячеек можно объединить в одну с помощью символа «амперсанд» (&) или функции СЦЕП.

Объединение данных с помощью символа «амперсанд» (&)

1. Выделите ячейку, в которую вы хотите вставить объединенные данные.
2. Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.
3. Введите символ & и пробел, заключенный в кавычки.
4. Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&» «&B2.

Объединение данных с помощью функции СЦЕП

1. Выделите ячейку, в которую вы хотите вставить объединенные данные.
2. Введите выражение = СЦЕП(.
3. Выберите первую ячейку, которую нужно объединить. Для разделения объединяемых ячеек используйте запятые, а для добавления пробелов, запятых и другого текста — кавычки.
4. Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, » Семья»).

Excel: сцепка несколько текстовых ячеек

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

1 способ – оператор &

1. Выделите ячейку, которая будет содержать итоговое значение.
2. В строке функций укажите ячейки =A1&B1&C1. в которых хранится информация.

1. Нажмите Enter

Если в текст нужно вставить пробел, то в формулу необходимо вставить » » .

Вместо ссылки на ячейку можно ввести текст =«Текст1»&А1 …

2 способ – функция = Сцепить( )

Можно набрать название функции вручную, для этого в строке функций ввести.

= Сцепить( ) и в скобках указать необходимые ячейки или текст.

Либо можно воспользоваться Мастером функций.

1. Выделите ячейку, которая будет содержать итоговое значение.
2. Нажмите кнопку Вставка функции на панели инструментов.

В появившемся диалоговом окне выберите Категорию «Текстовые» и выберите функцию Сцепить из списка. Нажмите ОК.

1. Заполните аргументы функции, указав ссылки на ячейки, или формулы, возвращающие текст, или текстовые значения в том порядке, в каком они должны быть объединены, не забывая о пробелах и знаках препинания. Нажмите ОК.

Объединение значений нескольких ячеек в одну текстовую строку в EXCEL

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

Когда значений немного, то объединить их можно с помощью функции СЦЕПИТЬ() записав формулу =СЦЕПИТЬ(A6;A7;A8;A9)

Или с помощью Амперсанда & (добавим еще для красоты символ пробела): =A6&» «&A7&» «&A8&» «&A9

Если значений много, то придется вводить вручную много амперсандов и аргументов функции СЦЕПИТЬ() . Это неудобно.

Избежать этого поможет дополнительный столбец с простейшей формулой:

Значение из последней строки будет содержать желаемую текстовую строку (см. файл примера ).

Возвращаясь к функции СЦЕПИТЬ() можно обнаружить, что она может вернуть несколько значений в одной строке и без утомительного ввода многочисленных аргументов. Для этого запишем формулу =СЦЕПИТЬ(A6:A9) , указав в качестве единственного аргумента весь диапазон сразу, а не отдельные ячейки. В результате получим лишь значение первой ячейки.

Теперь в Строке формул выделим формулу =СЦЕПИТЬ(A6:A9) и нажмем клавишу F9 . Получим массив =, его можно скопировать в буфер обмена (без знака = и фигурных скобок) и вставить в нужную ячейку.

От наборов символов «:» (двойные кавычки, двоеточие и опять двойные кавычки) можно избавиться с помощью функции ПОДСТАВИТЬ() (см. файл примера ). или с помощью инструмента Заменить (нажмите CTRL+H , чтобы его вызвать). Если строка Вася»:»Петя»:»Даша»:»Маша находится в ячейке G8 , то формула для замены этих символов, например, на символ ; будет выглядеть так:

В итоге, вставив результат формулы как значение, получим строку Вася;Петя;Даша;Маша.

Примечание : В формуле для замены двойных кавычек » использовано 2 двойные кавычки «».

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