Как при сцеплении ячеек поставить пробел
Перейти к содержимому

Как при сцеплении ячеек поставить пробел

  • автор:

покупка

Как объединить ячейки и добавить пробел между словами в Excel?

Обычно, когда вы используете функцию СЦЕПИТЬ для объединения нескольких значений ячеек, содержимое объединяется без пробелов. Но иногда вам нужно добавить пробелы, чтобы разделить содержимое после конкатенации. В этом случае, как можно объединить несколько ячеек в одну и добавить пробелы между значениями в Excel?

Concatenate multiple cell values and add space or other delimiters between words:

With Kutools for Excel‘s Combine utility, you can quickly combine multiple rows, columns or cells into a column, row or a cell with a specific separator as you need.

doc add space in concatenate 7

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

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

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

док добавить пробел в конкатенацию 1

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

1. Введите эту формулу = СЦЕПИТЬ (A2; «»; B2; «»; C2) в пустую ячейку помимо ваших данных, E2, например, см. снимок экрана:

док добавить пробел в конкатенацию 2

2. Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и ячейки в той же строке были объединены пробелом сразу, см. Снимок экрана:

док добавить пробел в конкатенацию 3

Ноты:

1. Вы также можете применить это = A2 & «» & B2 & «» & C2 формула для объединения ячеек с пробелом.

2. В приведенных выше формулах A2, B2, C2 — это ячейки, которые вы хотите объединить, если необходимо объединить больше ячеек, вы просто добавляете ссылки после формулы, например: = СЦЕПИТЬ (A2; «»; B2; «», C2; «», D2, «», E2) .

3. Если вы хотите использовать другой разделитель после объединения, например, используйте запятую для разделения, вы можете изменить формулу на = СЦЕПИТЬ (A2; «;»; B2; «;»; C2) .

Объедините несколько значений ячеек и добавьте пробелы или другие разделители между словами с помощью Kutools for Excel

Если необходимо объединить несколько ячеек, приведенные выше формулы будут несколько сложными, здесь я могу представить вам мощный инструмент — Kutools for Excel, С его Объединяйте строки, столбцы или ячейки без потери данных утилита, вы можете быстро объединить несколько ячеек из строки или столбца в одну ячейку с любыми определенными разделителями.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выделите диапазон ячеек, которые вы хотите объединить на основе строки.

2. Нажмите Кутулс > Слияние и разделение > Комбинируйте строки, столбцы или ячейки без потери данных, см. снимок экрана:

3. В появившемся диалоговом окне выполните следующие операции:

(1.) Выберите один вариант, который вам нужно объединить ячейки на основе ниже Чтобы объединить выбранные ячейки в соответствии со следующими параметрами, в этом примере я выбираю Объединить столбцы;

(2.) Затем выберите разделитель, которым вы хотите отделить объединенное содержимое от Укажите разделитель;

(3.) Наконец, вы можете указать параметр для работы с объединенными ячейками, вы можете сохранить или удалить содержимое из объединенных ячеек, или вы также можете объединить эти объединенные ячейки.

док добавить пробел в конкатенацию 5

4. После завершения настроек нажмите Ok or Применить кнопку, и все выбранные ячейки были объединены в одну ячейку на основе строк, см. снимок экрана:

док добавить пробел в конкатенацию 6

С этой Сочетать вы также можете объединять ячейки на основе столбца и объединять все ячейки только в одну ячейку.

Демо: объедините несколько значений ячеек и добавьте пробелы или другие разделители между словами с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Статьи по теме:

Функция СЦЕПИТЬ в Excel: синтаксис и примеры использования

Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.

Синтаксис функции СЦЕПИТЬ

Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.

Общие правила использования функции СЦЕПИТЬ:

  1. Для разделения аргументов применяется точка с запятой. Пропуск или использование других знаков приведет к отображению в ячейке с результатом кавычек.
  2. Текстовые аргументы обязательно заключать в кавычки. В противном случае появляется ошибка – #ИМЯ? Числа и ссылки на ячейки – не нужно.
  3. Чтобы соединяемые элементы в строке результата отобразились с пробелом, пробелы нужно добавлять в формулу с функцией. С помощью двойных кавычек с пробелом внутри (“ “) или с помощью пробела после текстового аргумента (“Иванов ”;…).

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

Начнем с простейших примеров.

Данные для объединения:

Данные для объединения.

Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:

СЦЕПИТЬ.

Результат объединения значений в ячейках:

Результат.

Такой же результат получим с помощью амперсанда:

Результат 1.

Один из аргументов – текст. Формула: =СЦЕПИТЬ(«слесарь»; » «;A2;» «;B2;» «;C2). Результат:

Результат 2.

И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:

  1. Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат: Результат 3.
  2. Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат. Результат 4.
  3. Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2). Результат 5.
  4. Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.

Результат 6.

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

ТРАНСП.

Теперь преобразуем формулу массива, добавив функцию и заменив скобки.

Формула массива.

Обратная функция СЦЕПИТЬ в Excel

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

  • ЛЕВСИМВ (отображает заданную часть символов с начала строки);
  • ПРАВСИМВ (отображает заданную часть символов с конца строки);
  • ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
  1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь). ЛЕВСИМВ.
  2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца. ПРАВСИМВ.
  3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

ПСТР.

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

  • Создать таблицу
  • Форматирование
  • Функции Excel
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Макросы VBA
  • Скачать примеры

Как сцепить в Excel ячейки (склеить значения)

Необходимость склеивать или сцеплять в Excel ячейки возникает, когда например нужно склеить ячейки хранящие отдельно Фамилию Имя Отчество, когда нам нужно заполнить ячейки отчета данными хранящимися в разных ячейках или просто сцепить какие-то части. Чтобы решить этот вопрос можно воспользоваться одним из вариантов сцепки: либо воспользоваться функцией Excel =СЦЕПИТЬ() либо английским «&».

В первом случае в самой функции СЦЕПИТЬ мы должны через точку запятую указать интересующие нас ячейки:

=СЦЕПИТЬ(Текст1;Текст2;Текст3;…) всего может быть от 1 до 255 аргументов функции.

Аналогичную операцию склейки можно выполнить так:

=«Текст1″&«Текст2«&«Текст3«&

либо сослаться на ячейки:

= A1 & B1 & C1 …

если, например, между текстом нужно вставить пробел или нижнее подчеркивание, то наши данные мы будем сцеплять с «_» или » «.

Данная операция одинаково выполнима во всех версиях Excel.

сцепить данные в Excel

Как сцепить данные в Excel

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

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Формула СЦЕПИТЬДИАПАЗОН

Функция =СЦЕПИТЬДИАПАЗОН(ДИАПАЗОН, [РАЗДЕЛИТЕЛЬ]) имеет два аргумента:

  • ДИАПАЗОН — диапазон ячеек, которые необходимо сцепить.
  • [РАЗДЕЛИТЕЛЬ] — символ-разделитель, который будет вставляться между значениями ячеек. Данный аргумент является не обязательным.

Аналогичная функция появилась только в Excel 2016. Если у Вас Excel ранней версии, то надстройка полностью исправляет это упущение разработчиков.

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

Пример 1

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

Этот пример можно сделать также с помощью стандартной функции СЦЕПИТЬ, однако для этого придется перечислить все ячейки диапазона. Если их две-три, то это не проблема, а вот если их гораздо больше, то выбор в пользу функции СЦЕПИТЬДИАПАЗОН думаю очевиден.

Пример 2

Перечислить все фамилии через запятую.

Обратите внимание, что в качестве разделителя указана запятая с пробелом («, «). Если не указать пробел, то все ячейки «сольются».

Пример 3

Можно сцепить более одного столбца. При этом сцепление ячеек происходит построчно.

Пример 4

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

Код на VBA

Код короткий, но весьма полезный!

Public Function СЦЕПИТЬДИАПАЗОН(ByRef ДИАПАЗОН As Excel.Range, Optional ByVal РАЗДЕЛИТЕЛЬ As String = "") As String Dim rCell As Range Dim MergeText As String For Each rCell In ДИАПАЗОН If rCell.Text <> "" Then MergeText = MergeText & РАЗДЕЛИТЕЛЬ & rCell.Text End If Next MergeText = Mid(MergeText, Len(РАЗДЕЛИТЕЛЬ) + 1) СЦЕПИТЬДИАПАЗОН = MergeText End Function

Надстройка
VBA-Excel

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *