Skip to content
В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на текст и числа.
Разделение текста из одной ячейки на несколько — это задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel с помощью функции «Текст по столбцам» и «Мгновенное заполнение». Сегодня мы подробно рассмотрим, как можно разделить текст по ячейкам с помощью формул.
Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.
Для преобразования текста в ячейках в Excel ключевым моментом является определение положения разделителя в нем. Что может быть таким разделителем? Это запятая, точка с запятой, наклонная черта, двоеточие, тире, восклицательный знак и т.п. И, как мы далее увидим, даже целое слово.
- Как распределить ФИО по столбцам
- Как использовать разделители в тексте
- Разделяем текст по переносам строки
- Как разделить длинный текст на множество столбцов
- Как разбить «текст + число» по разным ячейкам
- Как разбить ячейку вида «число + текст»
- Разделение ячейки по маске (шаблону)
- Использование инструмента Split Text
В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра букв) или НАЙТИ (с учетом регистра).
Как только вы определите позицию разделителя, используйте функцию ЛЕВСИМВ, ПРАВСИМВ и ПСТР, чтобы извлечь соответствующую часть содержимого.
Для лучшего понимания пошагово рассмотрим несколько примеров.
Делим текст вида ФИО по столбцам.
Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек, который демонстрируется ниже.
В столбце A нашей таблицы записаны Фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.
Можно сделать это при помощи инструмента «Текст по столбцам». Об этом методе мы достаточно подробно рассказывали, когда рассматривали, как можно разделить ячейку по столбцам.
Кратко напомним:
На ленте «Данные» выбираем «Текст по столбцам» — с разделителями.
Далее в качестве разделителя выбираем пробел.
Обращаем внимание на то, как разделены наши данные в окне образца.
В следующем окне определяем формат данных. По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру.
В итоге имеем следующую картину:
При желании можно дать заголовки новым столбцам B,C,D.
А теперь давайте тот же результат получим при помощи формул.
Для многих это удобнее. В том числе и по той причине, что если в таблице появятся новые данные, которые нужно разделить, то нет необходимости повторять всю процедуру с начала, а просто нужно скопировать уже имеющиеся формулы.
Итак, чтобы выделить из нашего ФИО фамилию, будем использовать выражение
=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)
В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.
Далее будет чуть сложнее.
Нужно извлечь второе слово, то есть имя. Чтобы вырезать кусочек из середины, используем функцию ПСТР.
=ПСТР(A2; ПОИСК(» «;A2) + 1; ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) — 1)
Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:
ПСТР (текст; начальная_позиция; количество_знаков)
Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:
- Начальная позиция — это позиция первого пробела плюс 1:
ПОИСК(» «;A2) + 1
- Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:
ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1
В итоге имя у нас теперь находится в C.
Осталось отчество. Для него используем выражение:
=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(» «; A2; ПОИСК(» «; A2) + 1))
В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.
Вот результат нашей работы по разделению фамилии, имени и отчества из одной по отдельным ячейкам.
Распределение текста с разделителями на 3 столбца.
Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.
- Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:
=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)
Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.
- Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:
=ПСТР(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«;A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)
Логику работы ПСТР мы рассмотрели чуть выше.
- Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:
=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(«-«; A2; ПОИСК(«-«; A2) + 1))
Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.
Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую («,») в ваших выражениях и используйте +2 и -2 вместо +1 и -1.
Как разбить текст по переносам строки.
Чтобы разделить слова в ячейке по переносам строки, используйте подходы, аналогичные тем, которые были продемонстрированы в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция СИМВОЛ (CHAR) для передачи символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу с клавиатуры.
Предположим, ячейки, которые вы хотите разделить, выглядят примерно так:
Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш 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))
Результат вы видите на скриншоте выше.
Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.
Как распределить текст с разделителями на множество столбцов.
Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»
Если действовать методами, описанными выше, то формулы будут просто мега-сложными. Вероятность ошибки при их использовании очень велика. Поэтому мы применим другой метод.
Имеем список наименований одежды с различными признаками, перечисленными через дефис. Как видите, таких признаков у нас может быть от 2 до 6. Делим текст в наших ячейках на 6 столбцов так, чтобы лишние столбцы в отдельных строках просто остались пустыми.
Для первого слова (наименования одежды) используем:
=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)
Как видите, это ничем не отличается от того, что мы рассматривали ранее. Ищем позицию первого дефиса и отделяем нужное количество символов.
Для второго столбца и далее понадобится более сложное выражение:
=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1);1)-1);»»)
Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).
А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в 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. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.
=ЛЕВСИМВ(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}. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.
Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER.
Как только позиция первой цифры найдена, вы можете разделить буквы и числа, используя очень простые формулы ЛЕВСИМВ и ПРАВСИМВ.
Чтобы получить текст:
=ЛЕВСИМВ(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 отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем также есть пробелы. Также есть пробелы в тексте пояснения, который также должен весь находиться слитно в одном столбце.
Решением является разбиение строки по следующей маске: * ERROR: * Exception: *
Здесь звездочка (*) представляет любое количество символов.
Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
То есть в данном случае в качестве разделителя по столбцам выступают не отдельные символы, а целые слова.
Итак, в начале ищем позицию первого разделителя.
=ПОИСК(«ERROR:»;A2;1)
Затем аналогичным образом находим позицию, в которой начинается второй разделитель:
=ПОИСК(«Exception:»;A2;1)
Итак, для ячейки A2 шаблон выглядит следующим образом:
С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.
Таким образом, в первый столбец мы поместим первые 20 знаков:
=—ЛЕВСИМВ(A2;ПОИСК(«ERROR:»;A2;1)-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:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.
Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.
Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек в Excel, который демонстрируется в следующей части этого руководства.
Как разделить ячейки в Excel с помощью функции разделения текста Split Text.
Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:
- Разделить ячейку по символу-разделителю.
- Разделить ячейку по нескольким разделителям.
- Разделить ячейку по маске (шаблону).
Чтобы было понятнее, давайте более подробно рассмотрим каждый вариант по очереди.
Разделить ячейку по символу-разделителю.
Выбирайте этот вариант, если хотите разделить содержимое ячейки при каждом появлении определённого символа .
Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:
- Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (Split Text)» на вкладке «Ablebits Data».
- Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу «Разбить по символам (Split by Characters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
- Выберите, как именно разбивать ячейки: по столбцам или строкам.
- Нажмите кнопку «Разделить (Split)» .
Примечание. Если в ячейке может быть несколько последовательных разделителей (например, более одного символа пробела подряд), установите флажок « Считать последовательные разделители одним».
Готово! Задача, которая требовала 3 формул и 5 различных функций, теперь занимает всего пару секунд и одно нажатие кнопки.
Разделить ячейку по нескольким разделителям.
Этот параметр позволяет разделять текстовые ячейки, используя любую комбинацию символов в качестве разделителя. Технически вы разделяете строку на части, используя одну или несколько разных подстрок в качестве границ.
Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:
В данном случае в качестве разделителей мы используем запятую и союз “или”.
В результате исходная фраза разделяется при появлении любого разделителя:
Примечание. Союзы «или», а также «и» часто могут быть частью слова в вашей исследуемой фразе, так что не забудьте ввести пробел до и после них, чтобы предотвратить разрывы слов на части.
А вот еще один пример. Предположим, вы импортировали столбец дат из внешнего источника, и выглядит он следующим образом:
5.1.2021 12:20
9.8.2021 14:50
Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:
- Точка (.) Для разделения дня, месяца и года
- Двоеточие (:) для разделения часов и минут
- Пробел для разграничения даты и времени
Нажмите кнопку Split, и вы сразу получите результат:
Разделить ячейки по маске (шаблону).
Эта опция очень удобна, когда вам нужно разбить список однородных строк на некоторые элементы или подстроки.
Сложность заключается в том, что исходный текст не может быть разделен при каждом появлении заданного разделителя, а только при некоторых определенных вхождениях. Следующий пример упростит понимание.
Предположим, у вас есть список строк, извлеченных из некоторого файла журнала. Чуть выше в этой статье мы разбивали этот текст по ячейкам при помощи формул. А сейчас используем специальный инструмент. И вы сами решите, какой из способов удобнее и проще.
Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и пояснительная информация, были в трех отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем имеются пробелы, которые должны отображаться в одном столбце, и есть пробелы в тексте пояснения, который также должен быть расположен в отдельном столбце.
Решением является разбиение строки по следующей маске:
* ERROR:* Exception: *
Где звездочка (*) представляет любое количество символов.
Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:
Примечание. При разделении строки по маске учитывается регистр. Поэтому не забудьте ввести символы в шаблоне точно так, как они отображаются в исходных данных.
Большое преимущество этого метода — гибкость. Например, если все исходные строки имеют значения даты и времени, и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:
* * ERROR:* Exception: *
Проще говоря, маска указывает надстройке разделить исходные строки на 4 части:
- Все символы перед 1-м пробелом в строке (дата)
- Символы между 1-м пробелом и словом ERROR: (время)
- Текст между ERROR: и Exception: (код ошибки)
- Все, что идет после Exception: (текст описания)
Думаю, вы согласитесь, что использование надстройки Split Text гораздо быстрее и проще, нежели использование формул.
Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вам интересно попробовать, ознакомительная версия доступна для загрузки здесь.
Вот как вы можете разделить текст по ячейкам таблицы Excel, используя различные комбинации функций, а также специальные инструменты. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Читайте также:
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… 
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
If your Excel cell or row contains two or more values, you can split that data across multiple cells. For Excel to divide the values, however, the cell must contain a delimiter, a character that specifies where the first number ends and the second one begins. For example, if the cell contains the data «123-456,» and you want to insert «123» into one cell and «456» into another, set the dash, or «-«, as the delimiter. If the cell contains «123, 456,» set the comma and space as delimiters. You can use the «Convert Text to Columns» wizard to split the desired data.
-
Choose the cell containing the data you want to split, and then click the «Data» tab.
-
Choose «Text to Columns» from the Data Tools group to launch the «Convert Text to Columns» wizard.
-
Select «Delimited,» and then click «Next.» Choose the appropriate delimiter from the options, or check «Other» and type the appropriate character into the corresponding field.
-
Check «Treat Consecutive Delimiters as One» if the delimiters are side-by-side, such as in the case of «123, 456.» Review your changes under Data Preview, and then click «Next.»
-
Set the column data format to «General,» and then click the «Destination» icon to specify in which cells the numbers should appear, or use the defaults.
-
Click «Finish» to complete the wizard and separate the numbers into the appropriate cells.
|
Как разложить число на цифры? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Предположим, вам нужно разбить номер на отдельные цифры, как показано на скриншоте ниже, что вы можете сделать для этого? Эта статья предоставит вам два метода.
Разбейте или разделите номер на отдельные цифры с помощью формулы
Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel
Разбейте или разделите номер на отдельные цифры с помощью формулы
В этом разделе будет показана формула для разделения выбранных числовых ячеек на отдельные цифры в Excel.
1. Выберите пустую ячейку (говорит ячейка C1), чтобы найти первую разделенную цифру числа в ячейке A1, затем введите формулу = MID ($ A1; COLUMN () — (COLUMN ($ C1) — 1); 1) в строку формул, а затем нажмите Enter .
Внимание: В формуле A1 — это ячейка с номером, который нужно разделить на цифры, а C1 — это ячейка для определения первой разделенной цифры. Пожалуйста, измените их по своему усмотрению.
2. Продолжайте выбирать ячейку C1, затем перетащите маркер заполнения в правые ячейки, пока все цифры ячейки A1 не будут разделены.
3. Не снимая выделения с этих ячеек с разделенными цифрами, перетащите маркер заполнения вниз к ячейкам, пока все числа не будут разделены на отдельные цифры. Смотрите скриншот:
Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel
Наблюдения и советы этой статьи мы подготовили на основании опыта команды Разделить клетки полезности Kutools for Excel помогает легко разделить все выбранные числовые ячейки на отдельные цифры сразу. Пожалуйста, сделайте следующее.
1. Выделите ячейки с номерами, которые нужно разбить на цифры, затем нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:
2. в Разделить клетки диалоговое окно, выберите Разделить на столбцы вариант в Тип и в разделе Указать разделитель выберите Указать ширину и введите число 1 в текстовое поле. Щелкните значок OK кнопку.
3. В следующем всплывающем окне Разделить клетки диалоговом окне укажите пустую ячейку для поиска первой цифры разделения, а затем щелкните OK кнопку.
После нажатия OK Кнопка, все числа в выбранных ячейках сразу же разбиваются на отдельные цифры, как показано на скриншоте ниже.
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (12)
Оценок пока нет. Оцените первым!
Содержание
- Способ 1: деление числа на число
- Способ 2: деление содержимого ячеек
- Способ 3: деление столбца на столбец
- Способ 4: деление столбца на константу
- Способ 5: деление столбца на ячейку
- Способ 6: функция ЧАСТНОЕ
- Вопросы и ответы
В Microsoft Excel деление можно произвести как при помощи формул, так и используя функции. Делимым и делителем при этом выступают числа и адреса ячеек.
Способ 1: деление числа на число
Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».
- Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно» (=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).
- Для того, чтобы произвести расчет и вывести его результат на монитор, делаем клик по кнопке Enter.
После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.
Если вычисление производится с несколькими знаками, то очередность их выполнения производится программой согласно законам математики. То есть, прежде всего, выполняется деление и умножение, а уже потом – сложение и вычитание.
Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».
Урок: Работа с формулами в Excel
Способ 2: деление содержимого ячеек
Также в Excel можно делить данные, находящиеся в ячейках.
- Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.
- Для того, чтобы произвести действие (деление), кликаем по кнопке «Enter».
Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.
Способ 3: деление столбца на столбец
Для расчета в таблицах часто требуется значения одного столбца разделить на данные второй колонки. Конечно, можно делить значение каждой ячейки тем способом, который указан выше, но можно эту процедуру сделать гораздо быстрее.
- Выделяем первую ячейку в столбце, где должен выводиться результат. Ставим знак «=». Кликаем по ячейке делимого. Набираем знак «/». Кликаем по ячейке делителя.
- Жмем на кнопку Enter, чтобы подсчитать результат.
- Итак, результат подсчитан, но только для одной строки. Для того, чтобы произвести вычисление в других строках, нужно выполнить указанные выше действия для каждой из них. Но можно значительно сэкономить своё время, просто выполнив одну манипуляцию. Устанавливаем курсор на нижний правый угол ячейки с формулой. Как видим, появляется значок в виде крестика. Его называют маркером заполнения. Зажимаем левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы.
Как видим, после этого действия будет полностью выполнена процедура деления одного столбца на второй, а результат выведен в отдельной колонке. Дело в том, что посредством маркера заполнения производится копирование формулы в нижние ячейки. Но, с учетом того, что по умолчанию все ссылки относительные, а не абсолютные, то в формуле по мере перемещения вниз происходит изменение адресов ячеек относительно первоначальных координат. А именно это нам и нужно для конкретного случая.
Урок: Как сделать автозаполнение в Excel
Способ 4: деление столбца на константу
Бывают случаи, когда нужно разделить столбец на одно и то же постоянное число – константу, и вывести сумму деления в отдельную колонку.
- Ставим знак «равно» в первой ячейке итоговой колонки. Кликаем по делимой ячейке данной строки. Ставим знак деления. Затем вручную с клавиатуры проставляем нужное число.
- Кликаем по кнопке Enter. Результат расчета для первой строки выводится на монитор.
- Для того, чтобы рассчитать значения для других строк, как и в предыдущий раз, вызываем маркер заполнения. Точно таким же способом протягиваем его вниз.
Как видим, на этот раз деление тоже выполнено корректно. В этом случае при копировании данных маркером заполнения ссылки опять оставались относительными. Адрес делимого для каждой строки автоматически изменялся. А вот делитель является в данном случае постоянным числом, а значит, свойство относительности на него не распространяется. Таким образом, мы разделили содержимое ячеек столбца на константу.
Способ 5: деление столбца на ячейку
Но, что делать, если нужно разделить столбец на содержимое одной ячейки. Ведь по принципу относительности ссылок координаты делимого и делителя будут смещаться. Нам же нужно сделать адрес ячейки с делителем фиксированным.
- Устанавливаем курсор в самую верхнюю ячейку столбца для вывода результата. Ставим знак «=». Кликаем по месту размещения делимого, в которой находится переменное значение. Ставим слеш (/). Кликаем по ячейке, в которой размещен постоянный делитель.
- Для того, чтобы сделать ссылку на делитель абсолютной, то есть постоянной, ставим знак доллара ($) в формуле перед координатами данной ячейки по вертикали и по горизонтали. Теперь этот адрес останется при копировании маркером заполнения неизменным.
- Жмем на кнопку Enter, чтобы вывести результаты расчета по первой строке на экран.
- С помощью маркера заполнения копируем формулу в остальные ячейки столбца с общим результатом.
После этого результат по всему столбцу готов. Как видим, в данном случае произошло деление колонки на ячейку с фиксированным адресом.
Урок: Абсолютные и относительные ссылки в Excel
Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.
Посмотрим применение данной функции на примере.
- Кликаем по ячейке, куда будет выводиться результат расчета. Жмем на кнопку «Вставить функцию» слева от строки формул.
- Открывается Мастер функций. В перечне функций, которые он нам предоставляет, ищем элемент «ЧАСТНОЕ». Выделяем его и жмем на кнопку «OK».
- Открывается окно аргументов функции ЧАСТНОЕ. Данная функция имеет два аргумента: числитель и знаменатель. Вводятся они в поля с соответствующими названиями. В поле «Числитель» вводим делимое. В поле «Знаменатель» — делитель. Можно вводить как конкретные числа, так и адреса ячеек, в которых расположены данные. После того, как все значения введены, жмем на кнопку «OK».
После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.
Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:
=ЧАСТНОЕ(числитель;знаменатель)
Урок: Мастер функций в Excel
Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш – «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.