Как разделить текст в одной ячейке на несколько ячеек в Excel
В руководстве объясняется, как разделить ячейки в Excel с помощью стандартных формул и инструментов. Вы узнаете, как отделять текст запятой, пробелом или любым другим разделителем, а также как разбивать строки на текст и числа.
Разделение текста в одной ячейке на несколько ячеек — задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel, используя текст по столбцам и мгновенное заполнение. Сегодня мы подробно рассмотрим, как можно разбить текст на ячейки с помощью формул.
Для разделения текста в Excel обычно используются функции ВЛЕВО, ВПРАВО или СРЕДНЯЯ в сочетании с ПОИСК или ПОИСК. На первый взгляд, некоторые из обсуждаемых ниже методов могут показаться сложными. Но логика достаточно проста и следующие примеры помогут вам в ней разобраться.
Чтобы преобразовать текст в ячейки в Excel, главное определить положение разделителя в нем. Что может быть таким разделителем? Это запятая, точка с запятой, косая черта, двоеточие, дефис, восклицательный знак и т.д. И, как мы увидим позже, даже полное слово.
- Как распределить ФИО по столбцам
- Как использовать разделители в тексте
- Разделение текста переносами строк
- Как разбить длинный текст на несколько столбцов
- Как разделить «текст + число» на разные ячейки
- Как разделить ячейку как «число + текст»
- Разделить ячейку по маске (шаблон)
- Использование инструмента разделения текста
В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра).
Как только вы определите позицию привязки, используйте функции LEFT, RIGHT и MID для извлечения соответствующей части содержимого.
Для лучшего понимания рассмотрим несколько пошаговых примеров.
Делим текст вида ФИО по столбцам.
Если изучение загадочных поворотов формул Excel не для вас, вам может понравиться визуальный метод разделения ячеек, показанный ниже.
Колонка А нашей таблицы содержит фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.
Вы можете сделать это с помощью инструмента «Текст в столбцы». Мы достаточно подробно обсуждали этот метод, когда рассматривали, как можно разбить ячейку на столбцы.
Коротко напомним:
На ленте «Данные» выберите «Текст по столбцам» — с разделителями.
Затем выберите пробел в качестве разделителя.
Обратите внимание на то, как наши данные разделены в окне примера.
В следующем окне мы определяем формат данных. По умолчанию это будет «Общий». Нас это вполне устраивает, поэтому оставляем как есть. Выберите верхнюю левую ячейку диапазона, в котором будет размещен наш разделенный текст. Если вам нужно сохранить исходные данные нетронутыми, лучше выбрать, например, B1.
В результате имеем следующее изображение:
При желании вы можете дать заголовки новым столбцам B, C, D.
Теперь давайте получим тот же результат, используя формулы.
Многим так удобнее. Даже по той причине, что если в таблице появятся новые данные, которые необходимо разбить, то не нужно повторять всю процедуру сначала, достаточно просто скопировать существующие формулы.
Итак, чтобы выбрать фамилию из нашего полного имени, мы будем использовать выражение
=ВЛЕВО(A2, ПОИСК(» «,A2,1)-1)
Мы используем пробел в качестве разделителя. Функция ПРОСМОТР сообщает нам, где находится первое место. И затем именно это количество букв (минус 1, чтобы не выделять сам пробел) мы «обрезаем» слева от нашего полного имени с помощью LEFT.
Кроме того, это будет немного сложнее.
Нам нужно извлечь второе слово, то есть имя. Чтобы разрезать кусок пополам, используйте функцию MID.
=СРЕДН(A2; ПОИСК(» «;A2) + 1; ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) — 1)
Как вы, наверное, знаете, функция MID в Excel имеет следующий синтаксис:
MID(текст; начальная_позиция; количество_символов)
Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:
- Начальная позиция — это позиция первого пробела плюс 1:
ПОИСК(» «;A2) + 1
- Количество символов для извлечения: разница между положением второго и первого пробелов минус 1:
ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1
В результате имя, которое у нас сейчас есть, написано на C.
Фамилия осталась. Для этого используем выражение:
=ВПРАВО(A2,ДЛСТР(A2) — ПОИСК(» «, A2, ПОИСК(» «, A2) + 1))
В этой формуле функция ДЛСТР возвращает общую длину строки, из которой вы вычитаете позицию второго пробела. Мы получаем количество символов после второго пробела, и функция ПРАВИЛЬНО извлекает их.
Это результат нашей работы по разделению фамилии, имени и отчества в отдельные ячейки.
Распределение текста с разделителями на 3 столбца.
Предположим, у вас есть список одежды в виде Название-Цвет-Размер и вы хотите разделить его на 3 отдельные части. Здесь слово-разделитель — дефис. Мы будем работать с ним.
- Чтобы извлечь название продукта (все символы до первого дефиса), вставьте следующее выражение в ячейку B2, а затем скопируйте его в столбец:
=ВЛЕВО(A2, ПОИСК(«-«,A2,1)-1)
Здесь функция сначала определяет позицию первого дефиса («-«) в строке, а LEFT извлекает из этой позиции все нужные символы. Вы вычитаете 1 из положения тире, потому что не хотите извлекать сам тире.
- Чтобы извлечь цвет (то есть все буквы между первым и вторым дефисом), напишите в C2, а затем скопируйте ниже:
=СРЕДН(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«,A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)
Логику работы PSTR мы рассмотрим чуть выше.
- Чтобы извлечь размер (все символы после третьего дефиса), введите в 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);»»)
Идея здесь в том, что с помощью функции ПОДСТАВИТЬ мы удаляем из исходного содержимого имя, которое мы уже извлекли (например, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-М-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″};»»))))
Чтобы извлечь буквы, вы подсчитываете, сколько их у нас есть. Для этого вычтем количество извлеченных цифр (C2) из общей длины исходной ячейки A2. После этого с помощью 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 цифр после содержимого ячейки A2. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге снова получаем массив из 10 цифр — номера позиций, в которых они были найдены. И из них функция MIN выбирает наименьшее число. Это будет позиция, с которой начинается группа цифр, которая должна быть отделена от основного содержания.
Также обратите внимание, что это формула массива и ее ввод необходимо завершить не как обычно, а комбинацией клавиш CTRL+SHIFT+ENTER.
Как только первая цифра найдена, вы можете разделить буквы и цифры, используя очень простые формулы LEFT и RIGHT.
Чтобы получить текст:
=ВЛЕВО(A2, B2-1)
Чтобы получить числа:
=ВПРАВО(A2, ДЛИНА(A2)-B2+1)
Где A2 — исходная строка, а B2 — позиция первого числа.
Чтобы избавиться от вспомогательного столбца, в котором мы вычисляем положение первой цифры, вы можете встроить MIN в функции LEFT и RIGHT:
Чтобы извлечь текст:
=ЛЕВЫЙ(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.
Далее вы должны получить код:
=MID(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
9.8.2021 14:50
Этот формат не распространен в Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разбить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле «Разнести по строкам:
- Точка (.) для разделения дня, месяца и года
- Двоеточие (:) для разделения часов и минут
- Пространство для разграничения даты и времени
Нажмите кнопку Разделить, и вы сразу получите результат:
Разделить ячейки по маске (шаблону).
Эта опция очень полезна, когда вам нужно разбить список однородных строк на несколько элементов или подстрок.
Сложность заключается в том, что исходный текст не может быть разделен при каждом появлении данного разделителя, а только в некоторых конкретных случаях. Следующий пример облегчит понимание.
Предположим, у вас есть список строк, извлеченных из некоторого файла журнала. Чуть ранее в этой статье мы разбили этот текст на ячейки с помощью формул. А теперь используем специальный инструмент. И вы сами решаете, какой способ удобнее и проще.
Вы хотите, чтобы дата и время, если применимо, код ошибки и пояснительная информация находились в трех отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем есть пробелы, которые должны отображаться в том же столбце, а также пробелы в тексте объяснения, которые также должны быть в отдельном столбце.
Решение состоит в том, чтобы разделить строку со следующей маской:
* ОШИБКА:* Исключение: *
Где звездочка (*) обозначает любое количество символов.
Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
Теперь нажмите кнопку «Разделить по обложке» на панели «Разделить текст», введите обложку в соответствующее поле и нажмите «Разделить».
Результат будет примерно таким:
Примечание. Разделение строки по маске чувствительно к регистру. Поэтому убедитесь, что вы вводите символы в шаблон точно так, как они отображаются в исходных данных.
Большим преимуществом этого метода является его гибкость. Например, если все ваши исходные строки имеют значения даты и времени, и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:
* * ОШИБКА:* Исключение: *
В двух словах, маска говорит плагину разделить исходные строки на 4 части:
- Все символы до первого пробела в строке (дата)
- Символы между 1-м пробелом и словом ОШИБКА: (время)
- Текст между ERROR: и Exception: (код ошибки)
- Все после Exception: (текст описания)
Я думаю, вы согласитесь, что использование плагина Split Text намного быстрее и проще, чем использование формул.
Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вы хотите попробовать его, пробную версию можно скачать здесь.
Вот так можно разделить текст на ячейки электронной таблицы Excel, используя различные комбинации функций, а также специальные инструменты. Спасибо за чтение, и мы надеемся увидеть вас в нашем блоге на следующей неделе!