Как разделить текст из одной ячейки на несколько ячеек в Excel
В учебнике объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделять текст запятыми, пробелами или другим разделителем, а также как разделять строки текста и числа.
разделение текста из одной ячейки на несколько ячеек — задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel, используя текст по столбцам и мгновенное заполнение. Сегодня мы подробнее рассмотрим, как можно разбить текст на ячейки с помощью формул.
Чтобы разбить текст в Excel, вы обычно используете функции ВЛЕВО, ВПРАВО или ЦЕНТР в сочетании с НАЙТИ или ПОИСК. На первый взгляд, некоторые из обсуждаемых ниже методов могут показаться сложными. Но логика на самом деле довольно проста, и следующие примеры помогут вам в ней разобраться.
Чтобы преобразовать текст в ячейках в Excel, необходимо определить положение разделителей внутри него. Что может быть таким разделителем? Это запятая, точка с запятой, косая черта, двоеточие, дефис, восклицательный знак и т д. И, как мы увидим далее, даже целое слово.
- Как распределить ФИО по столбцам
- Как расставить знаки препинания в тексте
- Разделяйте текст переносами строк
- Как разбить длинный текст на несколько столбцов
- Как разделить «текст + цифры» на разные ячейки
- Как разделить ячейку как «число + текст"
- Разделить ячейку по маске (шаблон)
- Использование инструмента «Разделить текст
В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (с учетом регистра) или НАЙТИ (с учетом регистра).
Как только вы определили положение разделителя, используйте функции ВЛЕВО, ВПРАВО и СРЕДНЯЯ, чтобы извлечь соответствующую часть содержимого.
Для лучшего понимания, давайте рассмотрим несколько примеров шаг за шагом.
Делим текст вида ФИО по столбцам.
Если вам не нравится разбираться в загадочных хитросплетениях формул Excel, вам может понравиться визуальный метод разделения ячеек, показанный ниже.
Столбец А в нашей таблице содержит фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.
Это можно сделать с помощью инструмента «Текст в столбцах». Об этом способе мы говорили достаточно подробно, когда рассматривали, как разбить ячейку на столбцы.
Помните карты:
На ленте «Данные» выберите «Текст по столбцам» — с разделителями.
Затем выберите пробел в качестве разделителя.
Обратите внимание, как наши данные разделены в окне примера.
В следующем окне мы определяем формат данных. По умолчанию это будет «Общий». Нас это вполне устраивает, поэтому оставляем как есть. Выберите верхнюю левую ячейку в области, где должен быть размещен наш разделенный текст. Если вам нужно сохранить исходные данные нетронутыми, лучше выбрать, например, B1.
В результате имеем следующее изображение:
При желании вы можете дать заголовки новым столбцам B, C, D.
Теперь давайте получим тот же результат, используя формулы.
Для многих это более практично. Включен по той причине, что если в таблице появятся новые данные, которые нужно расшарить, то не обязательно повторять всю процедуру сначала, а нужно только скопировать существующие формулы.
Итак, чтобы выбрать фамилию из нашего полного имени, мы будем использовать выражение
=ВЛЕВО(A2; ПОИСК(" ";A2;1)-1)
Мы используем пробел в качестве разделителя. Функция ПОИСК сообщает нам, в какой позиции находится первый пробел. И затем именно это количество букв (минус 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего полного имени с помощью ЛЕВЫЙ.
Кроме того, это становится немного сложнее.
Мы должны извлечь второе слово, то есть имя. Чтобы вырезать кусок из середины, используйте функцию MID.
=СРЕДНЕЕ(A2; ПОИСК(" ";A2) + 1; ПОИСК(" ";A2;ПОИСК(" ";A2)+1) - ПОИСК(" ";A2) - 1)
Как вы, наверное, знаете, функция Excel MID имеет следующий синтаксис:
MID(текст; начальная позиция; количество_символов)
Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:
- Исходное положение – это положение первого номера плюс 1:
ПОИСК(" ";A2) + 1
- Количество символов для извлечения: разница между позицией 2 и 1 пробела минус 1:
ПОИСК(" ";A2;ПОИСК(" ";A2)+1) - ПОИСК(" ";A2) – 1
В результате имя, которое мы теперь имеем в C.
Фамилия осталась. Для этого используем выражение:
=ВПРАВО(A2,ДЛИННОЕ(A2) - ПОИСК(" ", A2, ПОИСК(" ", A2) + 1))
В этой формуле функция ДЛСТР возвращает общую длину строки, из которой вы вычитаете вторую позицию пробела. Получаем количество символов через 2 пробела, и функция ПРАВИЛЬНО извлекает их.
Вот результат нашей работы по отделению фамилии, имени и отчества из одного в отдельные ячейки.
Распределение текста с разделителями на 3 столбца.
Предположим, у вас есть список одежды в виде Название-Цвет-Размер и вы хотите разбить его на 3 отдельные части. Здесь разделителем слов является дефис. Мы хотим работать с ним.
- Чтобы извлечь название продукта (все символы до 1 дефиса), вставьте следующее выражение в ячейку B2 и скопируйте его вниз по столбцу:
=ВЛЕВО(A2; ПОИСК("-",A2;1)-1)
Здесь функцией мы сначала определяем позицию первого дефиса ("-") в строке, а ЛЕВЫЙ извлекает из этой позиции все нужные символы. Вы вычитаете 1 из позиции дефиса, потому что не хотите извлекать сам дефис.
- Чтобы извлечь цвет (это все буквы между 1 и 2 тире), напишите в C2, а затем скопируйте ниже:
=СРЕДНЕЕ(A2; ПОИСК("-";A2) + 1; ПОИСК("-",A2;ПОИСК("-";A2)+1) - ПОИСК("-";A2) - 1)
Логику работы PSTR мы оценили чуть выше.
- Чтобы извлечь размер (все символы после 3 дефисов), введите в D2 следующее выражение:
=ВПРАВО(A2,ДЛИННОЕ(A2) - ПОИСК("-", A2, ПОИСК("-", A2) + 1))
Точно так же в Excel вы можете разделить содержимое ячейки на разные ячейки с помощью любого другого разделителя. Все, что вам нужно сделать, это заменить «-» на нужный символ, например пробел («»), косую черту («/»), двоеточие («:»), точку с запятой («;») и т д
Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству символов в разделителе. В нашем примере это дефис (то есть 1 символ). Если ваш разделитель состоит из двух символов, таких как запятая и пробел, просто укажите запятую ("",") в выражениях и используйте +2 и -2 вместо +1 и -1.
Как разбить текст по переносам строки.
Чтобы разделить слова в ячейке с помощью разрывов строк, используйте подходы, аналогичные показанным в предыдущем примере. Единственное отличие состоит в том, что вам нужна функция CHAR для передачи символа новой строки, так как вы не можете ввести его непосредственно в формулу с клавиатуры.
Допустим, ячейки, которые вы хотите разделить, выглядят примерно так:
Напомню, что перемещать текст внутри ячейки таким образом можно с помощью комбинации клавиш ALT+ENTER.
Возьмите инструкции из предыдущего примера и замените дефис («-») на CHAR(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))
Результат вы можете увидеть на скриншоте выше.
Точно так же вы можете работать с любым другим разделителем. Достаточно знать код.
Как распределить текст с разделителями на множество столбцов.
После изучения представленных выше примеров, думаю, у многих из вас возник вопрос: "А что делать, если у меня не 3 слова, а больше? Если нужно разделить текст в ячейке на 5 столбцов?»
Если действовать по методикам, описанным выше, формулы будут просто мегасложными. Вероятность ошибки при их использовании очень высока. Поэтому воспользуемся другим методом.
У нас есть список предметов одежды с разными функциями, перечисленный через дефис. Как видите, таких символов у нас может быть от 2 до 6. Мы делим текст в наших ячейках на 6 столбцов, чтобы лишние столбцы в отдельных строках просто оставались пустыми.
Для первого слова (название одежды) используем:
=ВЛЕВО(A2; ПОИСК("-",A2;1)-1)
Как видите, это ничем не отличается от того, что мы рассматривали ранее. Ищем позицию первого дефиса и отделяем нужное количество символов.
Для второго столбца и далее вам нужно более сложное выражение:
= ОШИБКА (ЛЕВАЯ КОНСТРУКЦИЯ (ЗАМЕНИТЬ ($ A2 & "-"; ПОДКЛЮЧИТЬ ("-"; ИСТИНА; $ B2: B2) & "-"; ""); 1); ПОИСК ("-"; ЗАМЕНИТЬ ($ A2 &"- " ;СОЕДИНИТЬ("-";ИСТИНА;$B2:B2)&"-";"";1);1)-1);"")
Идея заключается в том, что с помощью функции REPLACE мы удаляем из исходного содержимого имя, которое мы уже извлекли (например, «Юбка»). Вместо этого заменяем пустое значение "" и в результате имеем "Синий-М-39-42-50". В нем снова ищем позицию первого дефиса, как делали ранее. И с помощью ЛЕВОЙ снова выделяем первое слово (то есть "Синее»).
И тогда можно просто «растянуть» формулу из С2 по строке, то есть скопировать в остальные ячейки. В итоге получаем в D2
=ОШИБКА(ЛЕВАЯ(ЗАМЕНИТЬ($A2&"-"; ПОДКЛЮЧИТЬ("-";ИСТИНА;$B2:C2)&"-";"");1); ПОИСК("-";ЗАМЕНИТЬ($A2&"- " ;СОЕДИНИТЬ("-";ИСТИНА;$B2:C2)&"-";"";1);1)-1);"")
Обратите внимание, что изменения, произошедшие при копировании, выделены жирным шрифтом. Это означает, что теперь мы удаляем из исходного текста все, что было найдено и извлечено ранее — содержимое B2 и C2. И снова в полученной фразе берем первое слово — до дефиса.
Если брать больше нечего, функция ЕСЛИОШИБКА обработает это событие и вставит в результат пустое значение «».
Скопируйте формулы в строках и столбцах по мере необходимости. Результат вы можете увидеть на скриншоте.
Таким образом, вы можете разделить текст в ячейке на любое количество столбцов. Главное, чтобы использовались одинаковые разделители.
Как разделить ячейку вида 'текст + число'.
Начнем с того, что не существует универсального решения, которое будет работать для всех буквенно-цифровых выражений. Выбор зависит от конкретного шаблона, на который вы хотите разбить ячейку.Ниже приведены формулы для двух наиболее распространенных сценариев.
Допустим, у вас есть столбец со смешанным содержимым, в котором число всегда следует за текстом. Естественно, такое построение Excel считается символьным. Вы хотите разделить их, чтобы текст и числа отображались в отдельных ячейках.
Результат может быть достигнут двумя различными способами.
Метод 1. Подсчитайте цифры и извлеките это количество символов
Самый простой способ разбить выражение, в котором после текста стоит число:
Чтобы извлечь числа, вы ищете в строке все возможные числа от 0 до 9, получаете их общее количество и отсекаете такое же количество символов от конца строки.
Если мы работаем с ячейкой A2:
=ПРАВЫЙ(A2;СУММ(ДЛИННЫЙ(A2) - ДЛИННЫЙ(ЗАМЕНИТЬ(A2; {"0";"1";"2";"3";"4";"5";"6";"7" ;"8";"9"};""))))
Чтобы извлечь буквы, вы подсчитываете, сколько их у нас есть. Для этого вычтем количество извлеченных цифр (С2) из общей длины исходной ячейки А2. После этого с помощью LEFT отрезаем это количество символов от начала ячейки.
=ЛЕВО(A2;ЛЕВО(A2)-ЛЕВО(C2))
здесь A2 — исходная ячейка, а C2 — извлеченное число, как показано на снимке экрана:
Метод 2: узнать позицию 1- й цифры в строке
Альтернативным решением является использование этой матричной формулы для определения положения первой цифры:
{=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))}
Как видите, мы последовательно ищем каждое число из массива {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать ошибок, если цифра не найдена, добавляем эти 10 цифр после содержимого ячейки А2. Excel последовательно перебирает все символы в поисках десяти цифр. В итоге снова получаем матрицу из 10 цифр — номера позиций, где они были найдены. И из них функция MIN выбирает наименьшее число. Это будет позиция, с которой начинается группа цифр, которую необходимо отделить от основного содержания.
Также обратите внимание, что это формула массива и ее ввод нужно завершать не как обычно, а комбинацией клавиш CTRL+SHIFT+ENTER.
Как только положение первой цифры найдено, вы можете разделить буквы и цифры, используя очень простые формулы LEFT и RIGHT.
Чтобы получить текст:
=ВЛЕВО(A2; B2-1)
Чтобы получить числа:
=ВПРАВО(A2, ДЛИННОЕ(A2)-B2+1)
Где A2 — исходная строка, а B2 — позиция первого числа.
Чтобы избавиться от вспомогательного столбца, в котором мы вычисляли позицию первой цифры, можно ввести МИН в функции ВЛЕВО и ВПРАВО:
Как извлечь текст:
=ЛЕВЫЙ(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"};""))))
То есть мы сравниваем длину нашего текста без цифр с его исходной длиной, и получаем количество цифр, которое нужно взять справа. Например, если текст без цифр стал короче на 2 символа, нужно «отрезать» 2 символа вправо, что и будет нашим искомым номером.
А потом берем остальное:
=ЛЕВО(A2;ЛЕВО(A2)-ЛЕВО(C2))
Как видите, результат тот же. Вы можете использовать любой метод.
Как разделить ячейку вида 'число + текст'.
Если вы разделяете ячейки, содержащие буквы, числами, вы можете разделить числа, используя следующую формулу:
=ЛЕВО(A2;СУММ(ДЛИННОЕ(A2) - ДЛИННОЕ(ЗАМЕНИТЬ(A2; {"0";"1";"2";"3";"4";"5";"6";"7" ;"8";"9"};""))))
Это похоже на предыдущий пример, за исключением того, что вы используете функцию ВЛЕВО вместо функции ВПРАВО, чтобы получить число теперь из левой части выражения.
Теперь, когда у вас есть числа, разделите буквы, вычитая количество цифр из общей длины исходной строки:
=ВПРАВО(A2,ДЛИН.(A2)-ДЛИН.(B2))
Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:
Как разбить текст по ячейкам по маске (шаблону).
Эта опция очень полезна, когда вам нужно разбить список похожих строк на несколько элементов или подстрок. Сложность заключается в том, что исходный текст должен разбиваться не при каждом вхождении определенного разделителя (например, пробела), а только в некоторых конкретных вхождениях. Следующий пример облегчает понимание.
Предположим, у вас есть список строк, извлеченных из файла журнала:
Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и пояснительная информация находились в 3 отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем также есть пробелы. В пояснительном тексте также есть пробелы, которые также должны находиться в одной колонке.
Решение состоит в том, чтобы разбить строку по следующей маске: * ОШИБКА: * Исключение: *
Здесь звездочка (*) обозначает любое количество символов.
Двоеточие (:) включено в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
То есть в данном случае в качестве разделителя столбцов работают не отдельные символы, а целые слова.
Итак, в начале ищем позицию первого разделителя.
=ПОИСК("ОШИБКА:";A2;1)
Затем таким же образом находим позицию начала второго разделителя:
=ПОИСК("Исключение:";A2;1)
Таким образом, для ячейки A2 шаблон выглядит следующим образом:
Символы с 1 по 20 — это дата и время. Символы с 21 по 26 являются разделителями "ERROR:". Далее код ошибки. От 31 до 40 символов - второй разделитель "Exception:". Далее следует описание ошибки.
Поэтому в первую колонку поместим первые 20 символов:
=--ЛЕВО(A2;ПОИСК("ОШИБКА:";A2;1)-1)
Обратите внимание, что мы заняли на 1 позицию меньше начала первого разделителя. Кроме того, чтобы все это сразу преобразовать в дату, перед формулой ставим два знака минус. Это автоматически преобразует цифры в число, а дата будет сохранена в виде числа. Остается только ввести желаемый формат даты и времени стандартными средствами Excel.
Затем вам нужно получить код:
=СРЕДНЕЕ(A2;ПОИСК("ОШИБКА:";A2;1)+6;ПОИСК("Исключение:";A2;1)-(ПОИСК("ОШИБКА:";A2;1)+6))
Я думаю, вы понимаете, что 6 - это количество символов в нашем разделителе "ERROR:".
И, наконец, мы извлекаем объяснение из этого предложения:
=ПРАВО(A2,ДЛСТР(A2)-(ПОИСК("Исключение:",A2,1)+10))
Точно так же добавьте 10 к найденной позиции второго разделителя «Exception:», чтобы получить координаты первого символа сразу после разделителя. В конце концов, функция только сообщает нам, где начинается разделитель, а не заканчивается.
Таким образом мы разделили ячейку на 3 столбца, при этом исключив разделители.
Если вам не нравится разбираться в загадочных хитросплетениях формул Excel, вам может понравиться визуальный метод разбиения ячеек Excel, который демонстрируется в следующей части этого руководства.
Как разделить ячейки в Excel с помощью функции разделения текста Split Text.
Альтернативным способом разделения столбца в Excel является использование функции разделения текста, предоставляемой надстройкой Ultimate Suite for Excel. Он имеет следующие особенности:
- Разделить ячейку разделителем.
- Разделить ячейку несколькими разделителями.
- Разделить ячейку сеткой (шаблоном).
Чтобы было понятнее, давайте подробнее рассмотрим каждый вариант по очереди.
Разделить ячейку по символу-разделителю.
Выберите этот параметр, если хотите разбивать содержимое ячейки каждый раз, когда появляется определенный символ .
Для этого примера возьмем строки шаблона Product-Color-Size, которые мы использовали в первой части этого руководства. Как вы помните, мы разбили их на 3 разных столбца, используя 3 разные формулы. А вот как добиться того же результата за 2 простых шага:
- Предполагая, что у вас установлен Ultimate Suite, выберите ячейки, которые вы хотите разделить, и щелкните значок «Разделить текст» на вкладке «Данные Ablebits».
- Панель «Разделить текст» откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу «Разделить по символу» и выберите один из предустановленных разделителей или введите любой другой символ в поле «Пользовательский)» .
- Выберите способ разделения ячеек: по столбцам или по строкам.
- Нажмите кнопку «Поделиться)» .
Примечание. Если ячейка может иметь несколько последовательных разделителей (например, более одного пробела в строке), установите флажок «Обрабатывать последовательные разделители как один».
Прозрачный! Задача, для которой требовалось 3 формулы и 5 различных функций, теперь выполняется всего за несколько секунд и одним нажатием кнопки.
Разделить ячейку по нескольким разделителям.
Эта опция позволяет вам разделять текстовые ячейки, используя любую комбинацию символов в качестве разделителей. Технически вы разбиваете строку на части, используя одну или несколько разных подстрок в качестве разделителей.
Например, чтобы разбить предложение на части с помощью запятых и союзов, активируйте инструмент «Разделить по строкам» и введите разделители, по одному на каждую строку:
В этом случае мы используем запятую и союз «или» в качестве разделителей”.
В результате исходная фраза разделяется при появлении разделителя:
Примечание. Союзы «или», а также «и» часто могут быть частью слова в поисковой фразе, поэтому не забудьте поставить пробел до и после них, чтобы избежать разрывов слов.
А вот еще один пример. Допустим, вы импортировали столбец даты из внешнего источника, и он выглядит так:
5.1.2021 12:20
08.09.2021 14:50
Этот формат не распространен в Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Game of Strings:
- Точка (.) для разделения дня, месяца и года
- Двоеточие (:) для разделения часов и минут
- Пространство для ограничения даты и времени
Нажмите кнопку «Поделиться», и вы сразу получите результат:
Разделить ячейки по маске (шаблону).
Эта опция очень полезна, когда вам нужно разбить список однородных строк на несколько элементов или подстрок.
Сложность заключается в том, что исходный текст не может быть разделен при каждом вхождении данного разделителя, а только в некоторых конкретных случаях. Следующий пример облегчит понимание.
Предположим, у вас есть список строк, извлеченных из файла журнала. Чуть выше в этой статье мы разделили этот текст на ячейки с помощью формул. А теперь используем специальный инструмент. И вы сами решаете, какой способ практичнее и проще.
Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и пояснительная информация находились в трех отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем есть пробелы, которые должны отображаться в том же столбце, и есть пробелы в пояснительном тексте, которые также должны быть в отдельном столбце.
Решение состоит в том, чтобы разделить строку со следующей маской:
* ОШИБКА:* Исключение: *
Где звездочка (*) обозначает любое количество символов.
Двоеточие (:) включено в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
Теперь нажмите кнопку «Разделить по маске» на панели «Разделить текст», введите маску в соответствующее поле и нажмите «Разделить».
Результат будет примерно таким:
Примечание. Когда вы разделяете строку маской, она чувствительна к регистру. Поэтому обязательно вводите символы в шаблон точно так, как они отображаются в исходных данных.
Большим преимуществом этого метода является его гибкость. Например, если все исходные строки имеют значения даты и времени и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:
* * ОШИБКА:* Исключение: *
Проще говоря, маска говорит дополнению разбить исходные строки на 4 части:
- Все символы до 1 пробела в строке (дата)
- Символ между 1 пробелом и словом ОШИБКА: (время)
- Текст между ERROR: и Exception: (код ошибки)
- Все по исключениям: (текст описания)
Я думаю, вы согласитесь, что использование плагина Split Text намного быстрее и проще, чем использование формул.
Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вы хотите попробовать, пробную версию можно скачать здесь.
Вот так можно разделить текст в ячейках электронной таблицы Excel, используя различные комбинации функций и специальных инструментов. Спасибо за внимание и надеемся увидеть вас в нашем блоге на следующей неделе!
