Как в Excel разделить текст из одной ячейки в несколько

Как разделить текст из одной ячейки на несколько ячеек в Excel

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

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

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

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

 В зависимости от активности эту проблему можно решить с помощью функций ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра). 

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

Для лучшего понимания давайте рассмотрим несколько примеров шаг за шагом.

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

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

 В столбце А нашей таблицы указаны фамилии, имена и отчества сотрудников. Вам нужно разделить их на 3 столбца.

Вы можете сделать это с помощью инструмента «Текст по столбцам». Мы достаточно подробно говорили об этом методе, когда рассматривали, как разделить ячейку на столбцы.

Напомним вкратце:

На ленте «Данные» выберите «Текст по столбцам» - с разделителями.

Затем выберите пробел в качестве разделителя.

Обратите внимание на то, как наши данные разделены в окне примера.

В следующем окне мы определяем формат данных. По умолчанию это будет «Общие». Подходит идеально, поэтому оставляем как есть. Выберите верхнюю левую ячейку диапазона, в котором будет размещен наш разделенный текст. Если вам нужно оставить исходные данные нетронутыми, лучше выбрать, например, B1.

В результате мы имеем следующую картину:

При желании вы можете присвоить заголовки новым столбцам B, C, D.

Теперь мы получаем тот же результат по формулам.

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

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

= ВЛЕВО (LA2; ПОИСК (""; A2; 1) -1)

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

Также будет немного сложнее.

Вы должны извлечь второе слово - имя. Чтобы вырезать кусок из центра, используйте функцию MID.

= MID (A2; ПОИСК (""; A2) + 1; ПОИСК (""; A2, ПОИСК (""; A2) +1) - ПОИСК ("", A2) - 1)

Как вы, наверное, знаете, функция MID в Excel имеет следующий синтаксис:

MID (текст; начальное_число; число_символов)

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСКА:

• Начальная позиция - это позиция первого пробела плюс 1:

ПОИСК (""; LA2) + 1

• Количество извлекаемых символов: разница между положением 2-го и 1-го пробелов, минус 1:

ПОИСК (""; LA2; ПОИСК (""; LA2) +1) - ПОИСК (""; LA2) - 1

Следовательно, имя теперь на C.

Отчество остается. Для этого мы используем выражение:

= ВПРАВО (A2; DLSTR (A2) - ПОИСК (""; A2; ПОИСК (""; A2) + 1))

В этой формуле функция DLSTR (LEN) возвращает общую длину строки, из которой вычтена 2-я позиция пробела. Мы получаем количество символов после 2-го пробела, и функция ВПРАВО их извлекает.

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

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

Предположим, у нас есть список одежды, такой как Имя-Цвет-Размер, и мы хотим разделить его на 3 отдельные части. Здесь разделителем слов является дефис. Будем работать с ним.

1. Чтобы извлечь название продукта (все символы до первого тире), вставьте следующее выражение в B2, а затем скопируйте его в столбец:

= ВЛЕВО (LA2; ПОИСК ("-"; A2; 1) -1)

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

1. Чтобы извлечь цвет (это все буквы между 1-м и 2-м тире), напишите C2, а затем скопируйте ниже:

= MID (A2; ПОИСК ("-"; A2) + 1, ПОИСК ("-", A2, ПОИСК ("-", A2) +1) - ПОИСК ("-", A2) - 1)

Мы рассмотрели логику PSTR немного дальше.

1. Чтобы извлечь размер (все символы после 3-го тире), введите следующее выражение в D2:

= ВПРАВО (A2; DLSTR (A2) - ПОИСК ("-"; A2; ПОИСК ("-", A2) + 1))

Точно так же вы можете разделить содержимое ячейки на разные ячейки в Excel с любым другим разделителем. Все, что вам нужно сделать, это заменить "-" на нужный символ, например пробел (""), косую черту ("/"), двоеточие (":"), точку с запятой (";") и т.д.

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству символов в разделителе. В нашем примере это дефис (т.е. 1 символ). Если ваш разделитель состоит из двух символов, таких как запятая и пробел, включите в выражения только запятую (",") и используйте +2 и -2 вместо +1 и -1.

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

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

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

Напомню, что таким образом можно переносить текст внутри ячейки с помощью комбинации клавиш ALT + ENTER.

Воспользуйтесь инструкциями из предыдущего примера и замените тире («-») на CHAR (10), где 10 - это код ASCII для перевода строки.

Чтобы извлечь название продукта:

= ЛЕВЫЙ (A2; ПОИСК (АВТОМОБИЛЬ (10); A2; 1) -1)

Цвет:

= MID (A2; ПОИСК (CH (10), A2) + 1; ПОИСК (CH (10); A2; ПОИСК (CH (10), A2) +1) - ПОИСК (CH (10), A2) - 1)

Размер:

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

Результат вы можете увидеть на скриншоте выше.

Таким же образом можно работать с любым другим символом-разделителем. Просто знай код.

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

После изучения примеров, представленных выше, у многих из вас, я думаю, возникает вопрос: «Что, если у меня не 3 слова, а больше? Если вам нужно разделить текст в ячейке на 5 столбцов?»

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

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

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

= ВЛЕВО (LA2; ПОИСК ("-"; A2; 1) -1)

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

Для второго столбца и далее требуется более сложное выражение:

= ЕСЛИ ОШИБКА (LEFT (REPLACE ($ A2 & "-"; COMBINE ("-"; TRUE; $ B2: B2)) & "-"; ""; 1); SEARCH ("-"; REPLACE ($ A2 & "-"; COMBINE ("-"; ИСТИНА; $ B2: B2) & "-"; ""; 1); 1) -1);"")

Цель здесь в том, чтобы с помощью функции REPLACE мы удалили из исходного содержимого имя, которое мы уже извлекли (например, «Юбка»). Вместо этого мы подставляем пустое значение «» и в результате получаем «Blue-M-39-42-50». В нем мы снова ищем позицию первой черточки, как и раньше. И с помощью ЛЕВОГО снова выбираем первое слово (т.е. «Синее»).

И тогда вы можете просто «растянуть» формулу из C2 вдоль линии, т.е скопировать ее в остальные ячейки. В результате в D2 получаем

= ЕСЛИ ОШИБКА (LEFT (REPLACE ($ A2 & "-"; COMBINE ("-"; TRUE; $ B2: C2) & "-"; ""; 1); SEARCH ("-"; REPLACE ($ A2 & "-"; COMBINE ("-"; ИСТИНА; $ B2: C2) & "-"; ""; 1); 1) -1);"")

Обратите внимание, что изменения, внесенные во время копирования, выделены жирным шрифтом. То есть теперь мы удаляем из исходного текста все, что было найдено и извлечено ранее: содержимое B2 и C2. И снова в получившемся предложении берем первое слово - перед тире.

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

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

Таким образом, вы можете разделить текст в одной ячейке на любое количество столбцов. Главное - использовать одинаковые разделители.

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

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

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

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

Самый простой способ разбить выражение, в котором число стоит после текста:

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

Если мы работаем с ячейкой A2:

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

Чтобы извлечь буквы, вы подсчитываете, сколько у нас их. Для этого вычтите количество извлеченных цифр (C2) из ​​общей длины исходной ячейки A2. Затем, используя ВЛЕВО, мы сокращаем это количество символов от начала ячейки.

= ЛЕВЫЙ (A2; DLSTR (A2) -LSTR (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.

После того, как вы нашли положение первой цифры, вы можете разделить буквы и цифры, используя очень простые формулы ВЛЕВО и ВПРАВО.

Чтобы получить текст:

= ЛЕВЫЙ (A2; B2-1)

Чтобы получить числа:

= ВПРАВО (A2; DLSTR (A2) -B2 + 1)

Где A2 - исходная строка, а B2 - позиция первого числа.

Чтобы удалить вспомогательный столбец, в котором мы вычисляли позицию первой цифры, вы можете встроить MIN в функции LEFT и RIGHT:

Чтобы извлечь текст:

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

Для номеров:

= ПРАВО (A2; DLSTR (A2) -MIN (ПОИСК ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; A2 & "0123456789")) + 1)

Такого же результата можно добиться немного по-другому.

Сначала извлечем числа из ячейки, используя следующее выражение:

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

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

А потом берем остальное:

= ЛЕВЫЙ (A2; DLSTR (A2) -LSTR (C2))

Как видите, результат тот же. Вы можете использовать любой метод.

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

Если вы разделяете ячейки буквами после цифр, вы можете разделять числа, используя следующую формулу:

= ЛЕВЫЙ (A2; СУММ (DLSTR (A2) - DLSTR (ПОДСТАВИТЬ (A2; {"0"; "1"; "2"; "3"; "4"; "5"; "6"; "7" ; "8"; "9"};""))))

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

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

= ВПРАВО (A2; DLSTR (A2) -LSTR (B2))

Где A2 - это исходная строка, а B2 - это число, которое вы ищете, как показано на скриншоте ниже:

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

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

Предположим, у нас есть список строк, извлеченных из файла журнала:

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

Решение состоит в том, чтобы разделить строку в соответствии со следующей маской: * ОШИБКА: * Исключение: *

Здесь звездочка (*) обозначает любое количество символов.

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

То есть в данном случае не отдельные символы, а целые слова служат разделителем столбцов.

Итак, вначале ищем положение первого разделителя.

= ПОИСК ("ОШИБКА:"; A2; 1)

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

= ПОИСК ("Исключение:"; A2; 1)

Итак, для ячейки A2 модель выглядит так:

От 1 до 20 знаков - дата и время. От 21 до 26 знаков - разделитель «ОШИБКА:». Далее идет код ошибки. От 31 до 40 знаков - второй разделитель «Исключение:». Ниже приводится описание ошибки.

Поэтому мы поместим первые 20 символов в первый столбец:

= - ВЛЕВО (LA2; ПОИСК ("ОШИБКА:"; A2; 1) -1)

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

Далее вам нужно получить код:

= MID (A2; ПОИСК ("ОШИБКА:"; A2,1) +6, ПОИСК ("Исключение:"; A2; 1) - (ПОИСК ("ОШИБКА:"; A2,1) +6))

Думаю, вы понимаете, что 6 - это количество символов в нашем слове-разделителе «ОШИБКА:".

И наконец, давайте найдем объяснение из этого предложения:

= ВПРАВО (A2; DLSTR (A2) - (ПОИСК ("Исключение:"; A2; 1) +10))

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

Поэтому мы разделили ячейку на 3 столбца, исключая слова-разделители.

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

Как разделить ячейки в Excel с помощью функции разделения текста Split Text.

Альтернативный способ разбить столбец в Excel - использовать функцию разделения текста, включенную в надстройку Ultimate Suite для Excel. Он предоставляет следующие возможности:

• Разделить ячейку символом-разделителем.
• Разделите одну ячейку на несколько разделителей.
• Разделите ячейку по форме (шаблону).

Чтобы было понятнее, давайте подробнее рассмотрим каждый вариант по очереди.

Разделить ячейку по символу-разделителю.

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

Для этого примера возьмем строки шаблона Product-Color-Size, которые мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы. Вот как получить тот же результат за 2 быстрых шага:

1. Предполагая, что у вас установлен Ultimate Suite, выберите ячейки, которые вы хотите разделить, и щелкните значок «Разделить текст» на вкладке «Данные Ablebits».

1. Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
   • Разверните группу «Разбить по символам» и выберите один из предопределенных разделителей или введите любые другие символы в поле «Пользовательский)» .
   • Выберите, как именно разбивать ячейки: по столбцам или по строкам.
   • Нажмите кнопку «Разбить)» .

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

Готовый! Задача, которая раньше требовала 3 разных формул и 5 функций, теперь занимает всего пару секунд и нажатие кнопки.

Разделить ячейку по нескольким разделителям.

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

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

В этом случае мы используем запятую и союз «о» в качестве разделителей”.

В результате исходное предложение разделяется при отображении разделителя:

Примечание. Союзы «о» и «и» часто могут быть частью слова в целевом предложении, поэтому не забудьте поставить пробел до и после них, чтобы слова не распадались.

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

5.1.2021 12:20

9.9.2021 14:50

Этот формат не является распространенным в Excel, поэтому ни одна из функций даты здесь не распознает элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Оплачено в виде строк:

• Период (.) Для разделения дня, месяца и года
• Двоеточие (:) для разделения часов и минут
• Пространство для разделения даты и времени

Нажмите кнопку Split и вы сразу получите результат:

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

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

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

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

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

Решение состоит в том, чтобы разделить линию по следующей маске: 

* ОШИБКА: * Исключение: *

Где звездочка (*) представляет любое количество символов.

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

Теперь нажмите кнопку «Разделить по маске» на панели «Разделить текст», введите маску в соответствующее поле и нажмите «Разделить».
Результат будет примерно таким:

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

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

* * ОШИБКА: * Исключение: *

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

• Все символы перед 1-м пробелом в строке (дата)
• Символы между 1-м пробелом и словом ОШИБКА: (время)
• Текст между ERROR: и Exception: (код ошибки)
• Что-либо после исключения: (описательный текст)

Думаю, вы согласитесь, что использование надстройки «Разделить текст» намного быстрее и проще, чем использование формул.

Надеюсь, вам понравился этот быстрый и простой способ разбить строки в Excel. Если вы хотите попробовать, пробная версия доступна для загрузки здесь. 

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