3 слова из 1 ячейки разделить на несколько ячеек в экселе

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

На данном сайте вы найдете и другие статьи по работе с программой Excel.

 Делим текст вида ФИО по столбцам

Существует два способа, как разделить текст в excel по трем столбцам:

первый – визуальный;

второй — посредством создания комбинаций команд.

Рассмотрим оба метода, как разбить ФИО на три ячейки в excel более детально.

При отсутствии тяги к созданию формул, визуальный метод понравится вам своей понятностью и простотой.

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

Данное действие доступно с помощью меню «Данные» на ленте. Активируем кнопку «Текст по столбцам», в выпавшем меню делаем выбор «с разделителями».

На втором шаге программа предоставит вам выбор типа разделителей и даст возможность увидеть образец вида будущей таблицы.

В силу того, что в нашем случае, кроме пробела, слова не разделяет ничего – выбираем «пробел».

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

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

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

По итогу данные распределились следующим порядком:

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

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

Составленную один раз комбинацию команд можно неоднократно копировать в необходимые графы. При выполнении большого объема работ – это более удобный вариант.

Распределение текста с разделителями на 3 столбца

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

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

Шаг1: отделение фамилии

Для выделения из столбца ФИО фамилии используется самая простая из трех формул:

=ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)-1)

Рассмотрим поэтапно процесс ее создания, чтобы понять значимость и цель каждого вычисления.

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

Знаком «=» даем команду программе Эксель, что ячейка будет работать по прописанному сценарию, и вводим команду «ЛЕВСИМВ»

Далее активируем кнопку «Аргументы функции» для перехода в графическое окно редактирования формул.

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

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

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

Теперь, чтобы завершить задачу, и таблица правильно работала, активируем первую функцию и добавляем после второго аргумента -1. Таким образом, функция ПОИСК учитывала, что заключительным символом переносимых данных будет не пробел, а буква до него. Результатом является перенос в новый столбец только фамилии без дополнительных знаков.

Закрыв редактор, убедимся, корректен ли перенос информации.

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

Шаг 2: Разделение второго слова

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

=ПСТР(A2; ПОИСК(» «; A2)+1; ПОИСК(» «; A2; ПОИСК(» «; A2)+1)-ПОИСК(» «;A2)-1)

Для решения этой задачи используем формулу =ПСТР( — вписываем ее в запланированную ячейку и переходим в окно настройки аргументов.

Эта функция предназначена для поиска нужной строки текста, требующем разделения.

РИС.10

Начало строки укажем с помощью изученной уже команды ПОИСК().

Активировав «Аргументы функции», поле «текст» заполняем данными индекса ячейки с исходным набором символов, а в качестве искомого текста указываем выбранный символ-разделитель.

Теперь возвращаемся к функции «ПОИСК» и добавляем в конце «+1» для начала отсчета со следующего за найденным пробелом символа.

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

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

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

Далее возвращаемся к предыдущему «ПОИСКУ» и в поле «Начальная позиция» дописываем +1 к концу формулы для определения следующего за пробелом символа.

Следом активируем корень =ПСТР, уводим курсор к самому концу строки «Количество знаков» и дописываем выражение -ПОИСК(» «;A1)-1) для завершения определения границ, извлекаемого из исходного набора символов, слова.

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

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

Шаг 3: Разделение третьего слова

Завершающий этап – разделение третьего слова – «Отчество». Процесс схож с выделением фамилии, но основная формула немного отлична.

=ПРАВСИМВ(A2; ДЛСТР(A2)-ПОИСК(» «; A2; ПОИСК(» «;A2)+1))

В верхней строке заполняемого столбца вводим =ПРАВСИМВ( и активируем аргументы.

Вносим в поле «Текст» ячейку для разделения символов.

Сейчас вспомогательной будет функция ДЛСТР() с указанием той самой ячейки для разделения данных. Эта команда находит общее количество знаков. Останется только вырезать нужные.

Вносим далее –ПОИСК() и переходим к редактированию содержимого.

Указываем уже не раз вводимые данные для определения первого разделителя строки.

Добавляем еще один ПОИСК().

Указываем ему ту же структуру.

Возвращаемся к предыдущему «ПОИСКУ» и добавляем для его начальной позиции +1.

Сейчас активируем корень формулы ПРАВСИМВ и убеждаемся, что результат корректен, подтверждаем все внесенные изменения и растягиваем формулу вниз.

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

Далее рассмотрим более сложные и интересные варианты, как разделить в таблице excel текст по столбцам.

Прикладываю, готовое решение:

razd_texts

Как разбить текст по переносам строки

Для разделения слов по переносам строки используются аналогичные методы прошлых примеров. Процесс отличается только введением функции СИМВОЛ (CHAR), которая предназначена для передачи символа разрыва строки. Ввести с клавиатуры данную функцию невозможно. Поэтому рассмотрим создание формулы более детально для понимания всех этапов работы.

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

Распределить подобным образом символы внутри ячейки можно с помощью горячих клавиш ALT + ENTER.

Далее применяем инструкцию из предыдущего примера и заменяем «пробел» на СИМВОЛ(10), где 10 – это код функции ASCII для перевода строки.

Наименование одежды извлекаем по формуле:

=ЛЕВСИМВ(A2; ПОИСК(СИМВОЛ(10);A2;1)-1)

Следующий столбец определяем для переноса цвета изделия с помощью набора команд:

=ПСТР(A2;ПОИСК(СИМВОЛ(10);A2) +1;ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1)-ПОИСК(СИМВОЛ(10);A2)-1)

Завершаем процесс выделением размеров

=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2)+1))

Подобные разделения в таблице Эксель можно проводить с любыми символами-разделителями, зная их коды.

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

Освоив навыки составления формул по рассмотренным выше примерам, возникает вопрос: «Как в экселе можно разделить текст в ячейке, состоящий из большего количества слов, на несколько ячеек?».

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

Формулы для выделения центральных слов будут длинными и очень сложными. При их составлении высока вероятность ошибок. В данной ситуации применим иной метод.

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

Первое слово (наименование) вырезаем уже привычным способом – с помощью формулы

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

Здесь ничего не изменилось – нашли позицию первого разделителя и отделили нужное количество символов.

Для отделения второго слова (цвет) будет использоваться более сложное сочетание команд:

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1);1)-1);»»)

Смысл в том, что функция ПОДСТАВИТЬ удаляет из исходного текста наименование, которое ранее извлечено. Вместо, него подставляется пустое значение «». В остатке получается «Синий-М-39-42-50». Здесь снова применяется поиск первого разделителя, как раньше. Дальше, с применением ЛЕВСИМВ выделаем первое слово из вновь получившегося текста.

Затем можно просто скопировать формулу по строке, и программа внесет свои коррективы для правильной работы.

Формула ячейки D2 будет выглядеть уже так:

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)

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

Когда вырезать больше нечего, функция ЕСЛИОШИБКА оставит в качестве результата пустое место «».

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

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

Как разделить ячейку вида «текст + число»

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

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

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

Метод 1.  Подсчитайте цифры и извлеките это количество символов

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

Ищем в строке все цифровые символы от 0 до 9, находим их общее количество и отсекаем это количество знаков от конца строки.

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

где А2 – разделяемая ячейка, а С2 – извлеченное число цифр.

Метод 2: узнать позицию 1й цифры в строке

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

{=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))}

{0,1,2,3,4,5,6,7,8,9} в формуле определяет диапазон поиска цифр. Во избежание ошибок при отсутствии той или иной цифры, после содержимого ячейки добавляем эти символы. Эксель последовательно проверяет все символы на предмет наличия именно этих 10 и определяет их позиции в ячейке.

В свою очередь, функция МИН находит наименьшее число, которое является первым в отделяемой группе цифр.

Стоит помнить, что в excel ввод формулы массива для того, чтобы расцепить текст,  необходимо заканчивать комбинацией клавиш CTRL + SHIFT + ENTER, а не как обычно.

После определения позиции первой цифры, можно разделить символы с помощью простых формул ПРАВСИМВ и ЛЕВСИМВ.

для текста: =ЛЕВСИМВ(A2; B2-1)

для чисел: =ПРАВСИМВ(A2; ДЛСТР(A2)-B2+1)

Можно убрать вспомогательный столбец с вычислениями позиции первой цифры, включив МИН в функции ПРАВСИМВ и ЛЕВСИМВ:

Для буквенных символов:

=ЛЕВСИМВ(A2; МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))-1)

Для чисел:

=ПРАВСИМВ(A2; ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)

Этот же результат можно получить и другим путем.

Для начала, извлечь числа с помощью формулы:

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) -ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))

В результате использования разных вариантов получаем одно и то же. Поэтому можно воспользоваться любым, более удобным для пользователя методом.

Как разделить ячейку вида «число + текст».

Как разделить текст по столбцам в эксель, где буквы прописаны после цифр – процесс, аналогичный рассмотренному ранее. Только вместо функции ПРАВСИМВ применяется ЛЕВСИМВ, в связи с изменением позиций символов букв и цифр.

Числовые символы можно отделить по формуле:

=ЛЕВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2))

Как разбить текст по ячейкам по маске (шаблону).

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

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

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

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

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

В данной ситуации разделить текст по столбцам в excel позволит формула с применением маски: * ERROR: * Exception: *

Где знак (*) обозначает некоторое количество символов. Двоеточия (:) являются разделителями – они не должны отображаться в итоговых ячейках.

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

=ПОИСК(«ERROR:»;A2;1)

Далее идентифицируем позицию начала второго разделителя

=ПОИСК(«Exception:»;A2;1)

По итогу, шаблон ячейки содержит следующую последовательность:

• первые 20 символов – дата и время;
• с 21 по 26 знак – разделитель “ERROR:”
• код ошибки;
• с 31 по 40 знак – “Exception:” – он же второй разделитель
• пояснение.

Следовательно, первая графа будет содержать дату и время (первые 20 знаков).

=—ЛЕВСИМВ(A2;ПОИСК(«ERROR:»;A2;1)-1)

ВНИМАНИЕ! Перед функцией внесены два знака “-“ для конвертации всего выражения в дату – это автоматически составляет из цифр число. Кроме того, символы взяты на позицию меньше, чем начало первого разделителя.

Теперь осталось установить формат даты и времени инструментами Excel.

Следующим шагом извлекаем код:

=ПСТР(A2;ПОИСК(«ERROR:»;A2;1)+6;ПОИСК(«Exception:»;A2;1)-(ПОИСК(«ERROR:»;A2;1)+6))

Где 6 – количество знаков в разделителе «ERROR:».

И в заключение, отсекаем пояснение:

=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«Exception:»;A2;1)+10))

По аналогии с первым разделителем, добавляем 10, чтобы указать функции, где заканчивается разделитель «Exception:». Ведь функция видит только начальную координату разделения символов.

В итоге содержимое исходной ячейки распределено по трем столбцам без отражения разделителей.