Как расцепить данные в ячейке в excel

Распределение содержимого ячейки на соседние столбцы

Вы можете разделить содержимое ячейки и распределить ее части по нескольким смежным ячейкам. Например, если ваш сайт содержит столбец «Полное имя», его можно разделить на два столбца: столбец «Имя» и «Фамилия».

• Альтернативный способ распределения текста по столбцам см. в статье «Разделение текста по столбцам с помощью функций».
• Ячейки можно объединить с помощью функции СОВКА или С CONCATENATE.

Выполните указанные ниже действия:

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

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

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

Эта функция недоступна в Excel в Интернете.

Если у вас есть настольное приложение Excel, вы можете нажать кнопку «Открыть в Excel», чтобы открыть книгу и распределить содержимое ячейки на соседние столбцы.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как расцепить данные в ячейке в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Лучшая альтернатива функции СЦЕПИТЬ и объединить текст в Excel

Одна из самых популярных операций в Exel по работе с текстом – это объединение текстовых строк. Например, объединение нескольких слов в одну строку. На практике в процессе рутинной работы с Excel часто приходиться объединять Имена и Фамилии сотрудников в одну ячейку.

Как сцепить два текста в один с помощью и без функции Excel?

В примере показанным на рисунке таблица содержит первые два столбца:

1. Имя – ячейки в этой колонке содержат текстовые данные полученные из базы картотек кадрового отдела.
2. Фамилия – в данном столбце находиться только фамилии сотрудников.

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

Для этого необходимо попарно объедини тексты из ячеек столбцов таблицы в общие строки.

На рисунке проиллюстрированный пример решения данной задачи. Для этой цели в примере должен использоваться оператор «&» (END вместо оператора «+»), который позволяет объединять значения ячеек в формулах:

Как показано выше на рисунке с помощью символа «&» можно соединить любые текстовые значения из разных ячеек без использования функции: =СЦЕПИТЬ(). В этом примере объединены значения ячеек A2 и B2 с добавленным между ними символом пробела (указанный в формуле между кавычками как текстовое значение).

Для объединения текстов в одну строку программа Excel предлагает встроенную функцию
=СЦЕПИТЬ(A2;» «;B2), которая объединяет текстовые значения в одну строку. Однако на практике мы не рекомендуем использовать функцию СЦЕПИТЬ, а в место нее просто применять в формулах оператор &, так как она имеет ряд своих недостатков.

3 главных недостатка функции СЦЕПИТЬ по сравнению с оператором &:

1. Ограниченное максимальное количество объединяемых значений ячеек до 255.
2. Функция объединяет только текстовые значения, а оператор & объединит любые значения ячеек.
3. Функция требует немного больше затрат времени и ресурсов как у программы Excel (в процессе вычислений с использованием сложных формул), так и у пользователя (в процессе ввода функции в строку формул).

Интересный факт! В Excel нет обратной функции РАСЦЕПИТЬ и нет специального символа оператора. Для решения такой задачи применяются формулы с комбинациями текстовых функций. Например маркером для расцепления текста послужит символ пробела « » , а дальше:

• чтобы расцепить только имя используем формулу: =ЛЕВСИМВ(C2;ПОИСК(» «;C2;1))
• чтобы расцепить только фамилию – формула: =ПРАВСИМВ(C4;ПОИСК(» «;C4;1)+1)

Фактически функция СЦЕПИТЬ устарела, но все еще доступна пользователям в новых версиях программы Excel.

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

Текст по столбцам в Excel: как разделить и разбить?

Всем привет. В сегодняшнем интересном уроке мы поговорим про то, как разделить текст в ячейке Excel по столбцам. Представим себе ситуацию, что бухгалтер вам скинул не отформатированную таблицу с данными, где вам нужно определенные ячейки дополнительно разбить на еще несколько столбцов. Ладно если таблица не такая большая, и это можно сделать вручную. Но что делать, если данных там очень и очень много. В таком случае нам нужно разбить текст в готовые Excel ячейки по столбцам. В статье ниже мы рассмотрим два способа. Если при прочтении что-то будет не получаться – пишите в комментариях, и портал WiFiGiD.RU вам поможет. Поехали!

Способ 1: Специальная функция

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

1. Выделите те ячейки, которые хотите разбить. Используем для этого зажатую левую кнопку мыши (ЛКМ).
2. После этого на верхней панели переходим в раздел «Данные».

1. В этом разделе находим уже подраздел с названием «Работа с данными» – там жмем по кнопке «Текст по столбцам».

1. В первом шаге проверьте, чтобы стояла настройка «с разделителями» – то есть наши слова, которые мы хотим разбить должны быть разделены каким-то знаком (в нашем случае это пробел). Жмем «Далее».

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

1. На последнем шаге оставляем все как есть. Но прежде чем мы нажмем «Готово» – давайте прочитаем, что тут вообще происходит. На последнем шаге вам предлагается автоматически или вручную выставить форматы ячеек в Excel. По умолчанию выбрана «Общая» конфигурация, которая автоматически выставляет форматы (числовые, даты, текста и т.д.).

1. Как видите, теперь данные с ФИО разбиты по разным столбцам. Для наглядности можете добавить ячейку сверху с названием столбцов (Фамилия, Имя и Отчество).

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

Способ 2: Формула разделителя

Как я и говорил в конце прошлой главы, использовать встроенную функцию постоянно не очень удобно. Поэтому мы попробуем создать отдельную функцию – она будет разделять данные в ячейках, в которых будет находиться слова, разделенные пробелами. Дополнительно я научу вас создавать свои функции.

1. В одной части документа будут находиться наши данные, которые нам нужно разделить. Рядом я создал уже готовую таблицу, в которые эти данные будут разбиты и добавлены. Вы можете сделать также, или, например, наоборот сначала создать таблицу, а правее выделить место для строк с данными – это не имеет значения.

1. Сначала мы создадим функцию для захвата фамилии, поэтому кликаем по первой пустой строчке и вводим формулу:

1. После этого нажимаем по значку работы с функцией левее строки данных.

1. Выделяем строку «Текст» и с помощью ЛКМ указываем первую ячейку.

1. Во вторую строчку «Количество_знаков» – вписываем функцию:

1. Чтобы перейти к работе второй функции, нужно один раз ЛКМ кликнуть по этой функции в строке с данными.

1. Теперь мы уже работает со второй функцией. В «Искомый_текст» вписываем разделитель. Напоминаю, что он может быть любым знаком, но в нашем случае это пробел.
2. В «Текст_для_поиска» указываем первую ячейку.

1. Возвращаемся в работу с первой формулой, нажав по «ЛЕВСИМВ». Во вторую строчку допишите:

1. Зачем это нужно? – это нужно для того, чтобы наша сборная функция выделяла только слово без конечного пробела. Обратите внимание на конечные результаты после строчек, которые расположены после знака (=) – можно сказать это предварительные результаты. Как вы можете понять, функция выделяет строчку «Петров Петр Иванович». Далее мы находим расположение первого пробела – это 7 символ. Делаем вычитание, чтобы выделить только фамилию без пробела. В итоге функция возвращает только слово «Петров».

1. Применяем функцию, нажав «ОК». Теперь используем маркер автозаполнения, чтобы заполнить функцию во всех ячейках.

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

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

1. Выбираем вторую ячейку первого незаполненного столбца, вводим функцию и заходим в её настройки:

1. Тут все делаем по аналогии, как и в первом примере.

1. Указываем пробел и ячейку и обратно переходим в настройки ПСТР.

1. Дописываем (+1), ведь нам нужно расположение первой буквы имени. В строку «Количество_знаков» вписываем новый поиск и переходим к нему.

1. Вводим те же самые значения, но в последней строчке «Нач_позиция» вводим еще один ПОИСК.

1. Переходим уже в третий ПОИСК, указываем все то же самое.
2. Переключаемся на второй ПОИСК.

1. Нам нужно во втором поиске в последней строчке дописать:

1. Переходим в ПСТР.
2. Выделяем строчку «Количество_знаков».
3. Ставим курсор в самый конец формулы.
4. Копируем и вставляем вот эту функцию:

1. Применяем и растягиваем формулу на другие ячейки.

Если говорить проще, то функция работает так:

1. Мы находим расположение первого символа после первого пробела (или разделителя).
2. Далее нам нужно найти расположение последнего символа, который находится после следующего пробела.
3. В конце мы просто выделяем это слово и выводим в ячейке.

Теперь нам нужно уже найти последнее слово. Для этого нам нужно построить формулу:

В качестве домашнего задания – сделайте эту формулу вручную, как мы делали это при выводе фамилии и имени. Конечно, вы можете её просто скопировать и вставить, но в таком случае вы не поймете, как она работает.

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