Excel команду текст по столбцам - Word и Excel - помощь в работе с программами

Разделение текста на столбцы с помощью мастера распределения текста по столбцам

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

Проверьте, как это работает!

Выделите ячейку или столбец с текстом, который вы хотите разделить.
На вкладке Данные нажмите кнопку Текст по столбцам.
В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
Выберите разделители для своих данных. Например, запятую и пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.
Нажмите кнопку Далее.
В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.
Нажмите кнопку Готово.

См. также

Разделение текста по столбцам с помощью функций

Нужна дополнительная помощь?

Источник

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

В руководстве объясняется, как разделить ячейки в 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, используя различные комбинации функций, а также специальные инструменты. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Как использовать функцию «Текст по столбцам»

Все очень просто — щелкните «Данные» -> «Текст по столбцам»

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

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

Деление строки

Допустим, у нас есть такая табличка:

И, например, нам нужно разделить имя + фамилию и поместить их в разные столбцы.

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;

На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;

И на третьем этапе указываем куда поместить данные;

Подтверждаем.

Результат:

Примечание:

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

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

Делим электронную почту на имя пользователя и домен

Итак, в этом случае, мы будем использовать другой разделитель.

Допустим, у нас есть следующая табличка:

Я просто придумал эти почты исходя из фильмов по супергероям 🙂

Разделителем, как вы могли догадаться, в этом случае будет знак «@».

Давайте разделим строки.

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями», так как мы будем делить строку по знаку «@»;

На втором этапе, в опции «другой» укажите наш разделитель;

И укажите куда поместить данные после обработки;

Подтвердите.

Результат:

Получаем главный адрес сайта из ссылки

Бывает такое, что из множества ссылок вам нужно «вытащить» только главные адреса сайтов.

Например, в случае https://yandex.ru/primer1 главный адрес будет yandex.ru.

Итак, допустим у нас есть следующая табличка:

Давайте разделим строки и получим главные адреса сайтов!

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями»;

На втором этапе выберите «другой» и укажите «/»;

Укажите куда нужно поместить данные;

Подтвердите.

Результат:

Далее можно очистить таблицу от ненужных столбцов и дубликатов.

Важная информация: в случае, если ваши ссылки не имеют https:// в начале — все будет еще проще, вы получите главный адрес сайта в первом столбике.

Приведение дат к общему формату

Часто бывает так, что при импорте дат из разных источников они имеют разный формат.

Но что делать если этих данных очень много, а необходимо сделать так, чтобы все даты были в одном, «общем», формате?

Допустим, у нас есть такая табличка:

Давайте приведем все даты к одному формату!

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями»;

На втором этапе уберите все галочки;

На третьем этапе выберите опцию «дата — ДМГ» и укажите куда поместить обработанные данные;

Подтвердите.

Готово! Таким образом даты были отформатированы.

Смена формата с текстового на числовой

Бывают случаи, когда вы получаете от кого-то табличку, но не можете быстро произвести расчеты, так как некоторые числа в ней имеют текстовый формат.

Такое может быть по нескольким причинам:

Перед числом есть знак “ ‘ “;

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

Допустим, у нас есть такая табличка:

Давайте переведем их в формат «Числовой».

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями»;

На втором этапе уберите все галочки;

нужная опция «общий»;

Подтвердите.

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

Убираем несколько первых символов значения каждой ячейки

При работе в Excel вы можете столкнуться и с такой задачей.

Допустим, у нас есть такая табличка:

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

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «фиксированной ширины»;

На втором этапе — поставьте указатель на нужное количество символов (в нашем случае пять);

Укажите куда нужно поместить данные;

Подтвердите.

Результат:

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

Преобразуем числа со знаком минус на конце в отрицательные числа

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

Допустим, у нас есть такая табличка:

Давайте обработаем эти числа.

Как это сделать?

Пошаговая инструкция:

Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);

На первом этапе выбираем «с разделителями»;

На втором этапе уберите все галочки;

На третьем этапе щелкните на «Подробнее…»;

В открывшемся окне поставьте галочку на опции указанной на картинке ниже;

Укажите куда поместить обработанные данные;

Подтвердите.

Готово, теперь числа будут отрицательными. Можете использовать их в формулах.

Источник

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

Как выглядит склеенный текст в Excel

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Разделение текста на столбцы в Excel

Задача ясна, значит, сейчас предстоит разобраться с ней. Откройте лист и найдите там необходимый диапазон с данными. Если еще не вставили его в Excel, создайте пустой лист и сделайте это. После этого выполните такие действия:

Выделите только первый столбец, не затрагивая остальные строки (они и так являются пустыми, ведь символы просто не помещаются в рамку одной ячейки и переходят на другие). Перейдите на вкладку «Данные».
Вызовите инструмент «Текст по столбцам».
В большинстве случаев используется формат данных «С разделителями», который и следует отметить маркером, а затем перейти к следующему этапу в Мастере.
Снимите галочку с пункта «Знак табуляции», если в ваших строках применяется другой разделитель для значений. В случае когда действительно разделителем выступает табуляция, оставьте значение по умолчанию и сразу жмите «Далее».
Если сняли галочку со знака табуляции, выберите другой разделитель, использующийся в вашем списке. Программа предлагает три стандартных варианта и возможность указать свой.
В моем случае разделителем выступает косая черта (слэш) – нужно отметить галочкой «Другой» и напечатать этот знак в соответствующем поле. Как только вы это сделаете, ниже сразу отобразится результат выполнения операции.
Если при использовании в качестве разделителя выступают последовательные знаки, например кавычки, отметьте маркером пункт «Считать последовательные разделители одним» и из раскрывающегося списка выберите подходящий для вас вариант.
После нажатия по «Далее» отобразится последний шаг настройки, где вам предстоит задать формат данных в столбце. Этот этап можно пропустить, оставив значение «Общий».
Вернитесь к листу в Excel и убедитесь в том, что разделение прошло успешно. По надобности поправьте размеры ячеек и перенесите то содержимое, которое было отмечено другим разделителем и не посчиталось автоматически.

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

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

Конечно, это далеко не вся информация по теме. Если вы желаете более детально ознакомиться с ней, прочитайте другую мою статью – «Как работать в Excel с таблицами».

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

Источник

На чтение 7 мин Опубликовано 18.01.2021

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

Содержание

Необходимо разделить ФИО по отдельным столбцам
Разделение текста с помощью формулы
Этап №1. Переносим фамилии
Этап №2. Переносим имена
Этап №3. Ставим Отчество
Заключение

Необходимо разделить ФИО по отдельным столбцам

Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:

Открываем документ с допущенной ранее ошибкой.
Выделяем текст, зажав ЛКМ и растянув выделение до крайней нижней ячейки.

В верхней ленте находим «Данные» — переходим.

После открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и переходим в следующее диалоговое окно.

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

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

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

Замечание эксперта! Размещенный отформатированный текст из-за разного количества символов в ФИО может не вмещаться в выбранные ячейки, поэтому полученная таблица нуждается в корректировке. Для этого используется расширение размеров ячейки.

Разделение текста с помощью формулы

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

Этап №1. Переносим фамилии

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

Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.

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

Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».

Далее находим продолжение формулы ЛЕВСИМВ и кликаем по этой строке. Соглашаемся с выполненными действиями нажатием кнопки «ОК».
Появляется новое окно, где нужно указать адресацию ячейки, нуждающейся в корректировке. Для этого нажмите на графу «Текст» и активируйте необходимую ячейку. Адресация вносится автоматически.
Чтобы указать необходимое количество знаков, можно посчитать их вручную и вписать данные в соответствующую графу либо воспользоваться еще одной формулой: ПОИСК().
После этого формула отобразится в тексте ячейки. Кликните по ней, чтобы открыть следующее окно.

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

Соглашаемся и кликаем по кнопке «ОК».

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

Этап №2. Переносим имена

Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.

В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.

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

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

Возвращаемся к формуле =ПСТР в верхней части экрана.
В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.

Переходим к определению количества знаков – вписываем формулу ПОИСК().
Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.

Этап №3. Ставим Отчество

Активировав ячейку и перейдя в аргументы функции, выбираем формулу ПРАВСИМВ. Жмем «ОК».

В поле «Текст» вписываем адресацию редактируемой ячейки.
Там, где необходимо указать число знаков, пишем ДЛСТР(A2).

Примечание эксперта! Формула определит автоматически количество символов.

Для точного определения количества знаков в конце необходимо написать: -ПОИСК().
Перейдите к редактированию формулы. В «Искомый текст» укажите пробел. В «Текст для поиска» — адресацию ячейки. В «Нач.позиция» вставьте формулу ПОИСК(). Редактируйте формулу, установив те же самые значения.
Перейдите к предыдущему ПОИСК и строке «Нач.позиция» допишите +1.

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

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

Содержание

Способ 1: Использование автоматического инструмента
Способ 2: Создание формулы разделения текста
- Шаг 1: Разделение первого слова
- Шаг 2: Разделение второго слова
- Шаг 3: Разделение третьего слова
Вопросы и ответы

Способ 1: Использование автоматического инструмента

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

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

После этого перейдите на вкладку «Данные» и нажмите кнопку «Текст по столбцам».

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

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

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

Вернитесь к таблице и убедитесь в том, что разделение прошло успешно.

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

Способ 2: Создание формулы разделения текста

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

Шаг 1: Разделение первого слова

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

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

Выберите ячейку, где хотите расположить первое слово, и запишите формулу =ЛЕВСИМВ(.

После этого нажмите кнопку «Аргументы функции», перейдя тем самым в графическое окно редактирования формулы.

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

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

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

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

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

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

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

Полностью созданная формула имеет вид =ЛЕВСИМВ(A1;ПОИСК(" ";A1)-1), вы же можете создать ее по приведенной выше инструкции или вставить эту, если условия и разделитель подходят. Не забывайте заменить обрабатываемую ячейку.

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

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

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

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

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

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

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

Сейчас формула уже может начать поиск строки с первого символа имени, но она пока еще не знает, где его закончить, поэтому в поле «Количество_знаков» снова впишите формулу ПОИСК().

Перейдите к ее аргументам и заполните их в уже привычном виде.

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

Перейдите к созданной функции и заполните ее таким же образом.

Возвращайтесь к первому "ПОИСКУ" и допишите в «Нач_позиция» +1 в конце, ведь для поиска строки нужен не пробел, а следующий символ.

Кликните по корню =ПСТР и поставьте курсор в конце строки «Количество_знаков».

Допишите там выражение -ПОИСК(" ";A1)-1) для завершения расчетов пробелов.

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

Формула получилась большая, и не все пользователи понимают, как именно она работает. Дело в том, что для поиска строки пришлось использовать сразу несколько функций, определяющих начальные и конечные позиции пробелов, а затем от них отнимался один символ, чтобы в результате эти самые пробелы не отображались. В итоге формула такая: =ПСТР(A1;ПОИСК(" ";A1)+1;ПОИСК(" ";A1;ПОИСК(" ";A1)+1)-ПОИСК(" ";A1)-1). Используйте ее в качестве примера, заменяя номер ячейки с текстом.

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

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

В пустой ячейке для расположения будущего текста напишите =ПРАВСИМВ( и перейдите к аргументам этой функции.

В качестве текста указывайте ячейку с надписью для разделения.

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

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

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

Добавьте для начальной позиции еще один ПОИСК().

Ему укажите ту же самую структуру.

Вернитесь к предыдущей формуле «ПОИСК».

Прибавьте для его начальной позиции +1.

Перейдите к корню формулы ПРАВСИМВ и убедитесь в том, что результат отображается правильно, а уже потом подтверждайте внесение изменений. Полная формула в этом случае выглядит как =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(" ";A1;ПОИСК(" ";A1)+1)).

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

Смотрите также фамилию каждого сотрудника Marangoni *», блок6=»79Q». предусматреть! А Если — «дата». ячейке Иванова, во (» «). для хранения каждогоШтатMID найти символ пробелаЕсли «Мгновенное заполнение» включено, до Excel 2013,Вот что Вам нужно указать, куда поместить данных. Так какCountryВ этой статье Вы для последующего использования

Куда делось «TL»? после номера идетНажимаем кнопку «Готово». второй — Мария.Формула будет такая. составные части распределенныхАббревиатура(ПСТР) – извлекает в ячейке но не предлагает то можете воспользоваться сделать, чтобы разбить разделённые ячейки.В разделе записи разделены пробелами(Страна), найдёте несколько способов, в сводных таблицахЛегко определить начало трех значное число, Получилось так. Нам нужно написать

=A2&» «&B2&» «&C2 данных.Столица
часть текстовой строкиA2
никаких вариантов, которые преимуществами нового инструмента такой столбец на
Column data format и запятыми, мыArrival Date

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

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

(то есть заданноеи начнём поиск соответствуют определённому шаблону, « два:(Формат данных столбца) выбираем формат(Ожидаемая дата прибытия) или целые столбцы

программой Excel. блок6, основная сложность
знак обрезает! из одной ячейки

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

Мария, во второй только слова, но весь столбец, гдеAlabama, AL, Montgomery количество символов). Синтаксис: с первого символа. Вы можете запуститьМгновенное заполнениеЗапустите инструмент Вы можете выбрать

Delimited и в Excel 2010Для этого необходимо попарно для формулы -vikttur на большее количество — Иванова. Как и цифры. Можно содержатся текстовые значения,Alabama=MID(text,start_num,num_chars)Замечание: этот инструмент вручную» и заставить ExcelText to Columns

формат данных отдельно(С разделителями). ВариантStatus и 2013. Приведённые объедини тексты из определение места разрыва: Пока еще не столбцов. Например, здесь это сделать быстросоставить предложение из данных которые требуется разделить.AL=ПСТР(текст;начальная_позиция;количество_знаков)Если поиск начинается на вкладке автоматически заполнять (в(Текст по столбцам), для каждого столбца,Fixed width(Статус). примеры и скриншоты ячеек столбцов таблицы этих блоков. массивная, но если разделили дату отдельно в большой таблице, ячеек в ExcelНа вкладкеMontgomeryВ качестве аргументов функции с первого символа,Data нашем случае – как мы это в которые будут(Фиксированной ширины) будетЕсли в таблице есть иллюстрируют работу с в общие строки.Vanya7819 еще всплывут условия… по столбцам – смотрите в статье.ДанныеИзвлекаем название штата: указываем: какой текст

Вы можете вообще(Данные) > разбивать) данные, при делали в предыдущем помещены разделённые данные. рассмотрен чуть позже.

 хотя бы один инструментами «Текст поНа рисунке проиллюстрированный пример: TL сдесь не=ЕСЛИ(E2=B2;»»;ЕСЛИ(ЕЧИСЛО(—ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);3;1));ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;3);ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2))) число, месяц, год. «Как поменять местамиМожно установить формулыв группе=LEFT(A2,SEARCH(«,»,A2)-1) взять, позицию символа, пропустить аргументFlash Fill обнаружении определенной закономерности. примере. На первом

По умолчанию для Если все готово, столбец справа от столбцам» и «Мгновенное решения данной задачи. играет роли. ОсноваVanya7819Для большей наглядности столбцы в Excel». в нужных ячейках
Работа с данными=ЛЕВСИМВ(A2;ПОИСК(«,»;A2)-1) с которого нужноstart_num(Мгновенное заполнение) илиЕсли Вы ещё не шаге мастера выберите всех столбцов задан жмите столбца, который необходимо заполнение», кроме этого Для этой цели в 6 столбцах: Можно ли с в таблице ExcelЗдесь рассмотрим, бланка. Например, унажмите кнопкуИзвлекаем аббревиатуру штата: начать, и сколько
(нач_позиция) в формуле нажав сочетание клавиш знакомы с этой параметр
-  форматNext разбить, тогда первым Вы увидите подборку в примере должен (как в примере). помощью формул расделить можно выделить некоторыекак разделить текст в нас есть такойТекст по столбцам=MID(A2,SEARCH(«,»,A2)+2,SEARCH(«,»,A2,SEARCH(«,»,A2)+2)-SEARCH(«,»,A2)-2) символов извлечь. и упростить еёCtrl+E функцией, я попробуюFixed widthGeneral(Далее), чтобы продолжить. делом создайте новые формул для разделения использоваться оператор «&»Vanya7819 ячейки по столбцам строки, столбцы (например, ячейке Excel на список клиентов с.=ПСТР(A2;ПОИСК(«,»;A2)+2;ПОИСК(«,»;A2;ПОИСК(«,»;A2)+2)-ПОИСК(«,»;A2)-2)
- Вы можете использовать аналогичные до такого вида:. кратко объяснить её(Фиксированной ширины) и(Общий). Мы оставимНа следующем шаге определяем пустые столбцы, в имён, текстовых и (END вместо оператора: Буду очень ВАМ в данном примере? итоговые) выделить другим несколько ячеек. адресами.Следуйте инструкциям вИзвлекаем столицу штата: формулы, чтобы разбить=LEFT(A2,SEARCH(» «,A2)-1)Существуют формулы, которые могут суть. Этот инструмент нажмите его без изменений разделители, которые содержатся которые будут помещены числовых значений. Этот «+»), который позволяет благодарен, — распишитеБольшое СПАСИБО!!! цветом, разделительной линией.Например, предложение из
Нам нужно составить предложение.Столбцах мастера=RIGHT(A2,LEN(A2)-(SEARCH(«,»,A2,SEARCH(«,»,A2)+1)+1)) имена с суффиксами=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1) быть очень полезны, анализирует данные, которыеNext для первых трёх в данных, и полученные данные. Этот урок поможет Вам
объединять значения ячеек подробно, как работаетvikttur Подробнее, смотрите в первой ячейки разнести В ячейке пишем, чтобы указать способ=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«,»;A2;ПОИСК(«,»;A2)+1)+1)) в конце:LEFT когда возникает необходимость Вы вводите на(Далее). столбцов, а для ограничитель строк. шаг необходим для выбрать наилучший метод в формулах: каждая строка в: Можно. Но чем статье «Разделительная линия по словам в формулу. разбиения текста наА вот пример реальныхA(ЛЕВСИМВ) и разбить ячейки или рабочий лист, иВ разделе четвёртого столбца установимНастраиваем разделители того, чтобы результаты разбиения данных вКак показано выше на VBA (в Вашем больше различий в строк в таблице

несколько других ячеек,=A2&» «&B2&» «&C2&» разных столбцах. данных из ExcelBRIGHT столбцы с данными пытается выяснить, откудаData preview формат. Если данные разделены не были записаны Excel. рисунке с помощью примере, — можно расположении блоков и Excel» тут. ФИО, дату, т.д. «&»проживает по адресу»&»Примечание: 2010. Данные изC(ПРАВСИМВ) – возвращает в Excel. На они взялись и(Образец разбора данных)

Data одним или несколькими поверх уже существующихГоворя в общем, необходимость символа «&» можно в екселе). У в самих блоках,Можно разделить ячейкуНапример, у нас «&»г.»&» «&D2&» «&»ул.»&» Для получения справки по первого столбца разбитыD левую или правую

самом деле, следующих существует ли в настройте ширину столбцов.

Разбиваем текст фиксированной ширины по нескольким столбцам

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

по диагонали и, есть список с «&E2&» «&»д.»&» «&F2&».» заполнению все действия на три отдельных1 часть текста из

шести функций будет них какая-либо закономерность. Как видно на ведь в этот

выбрать все подходящие сразу после столбца Excel может возникнуть значения из разных по 25 вариантахДля блок3 и в каждой части фамилиями, именами вПолучилось такое предложение. мастера читайте статью столбца:Полное имя заданной ячейки соответственно.
достаточно в большинстве Как только «Мгновенное рисунке ниже, край столбец попадут даты варианты в разделеParticipant в двух случаях: ячеек без использования — в остальном блок4 достаточно написать текст. Читайте, одном столбце. НамПо такому принципу составляем Разделение текста поУрок подготовлен для ВасИмя Синтаксис формулы: случаев – заполнение» распознает Ваши столбца символизирует вертикальная прибытия.Чтобы изменить форматDнаходится столбец Во-первых, при импорте функции: =СЦЕПИТЬ(). В я постараюсь розобратся=ЛЕВСИМВ(A4;3) как это сделать, нужно написать фамилии любые предложения.
различным столбцам с командой сайта office-guru.ruФамилия=LEFT(text,[num_chars])LEFT действия и вычислит линия, и чтобы данных для каждогоelimiters

Разбиваем объединённые ячейки в Excel

Status информации из какой-либо этом примере объединены сам по ВАШИМ=ПСТР(A4;5;2) в статье «Как в одном столбце,Если текст в преобразовать мастера текстов,Источник: https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/Суффикс=ЛЕВСИМВ(текст;[количество_знаков])(ЛЕВСИМВ), закономерность, Excel предложит задать край следующего конкретного столбца, выделите(Символом-разделителем является) или, и мы собираемся внешней базы данных значения ячеек A2

подсказкам. «TL» -В блок5 перед разделить ячейку в а имена в ячейках уже написан, или нажмите кнопкуПеревел: Антон Андронов2В качестве аргументов указываем:MID вариант, и последовательность столбца, просто кликните его, кликнув по ввести свой вариант добавить между ними

Разделяем данные на несколько столбцов в Excel 2013 при помощи мгновенного заполнения

или с веб-страницы. и B2 с не играет роли R может быть Excel» здесь. другом столбце. Например, но нам нужноСправкаАвтор: Антон АндроновRobert Furlan Jr. какой текст взять

(ПСТР), записей в новом в нужном месте. нему в области разделителя в поле новые столбцы При таком импорте добавленным между ними — лучше бы пробел, нужно добавитьУдобнее работать в чтобы определить полвставить дополнительные словав мастере разделенияПримечание:Robert и сколько символовRIGHT столбце появится буквально Двойной щелчок по

Data previewOtherLast Name все записи копируются символом пробела (указанный его тоже забрать. функцию: таблице, где в людей в списке.перед имеющимися, то текста столбцов.Мы стараемся как

Furlan извлечь. В следующем(ПРАВСИМВ), за мгновение. вертикальной линии удалит(Образец разбора данных),(Другой).В нашем примере, в один столбец, в формуле между Спасибо!!!=ПОДСТАВИТЬ(ПСТР(A4;ПОИСК(«R»;A4)+1;3);» «;»») ячейках не видны Подробнее об этом

сделать это можноСписок в Excel можно оперативнее обеспечиватьJr. примере формула будетFINDТаким образом, при помощи край столбца, а а затем установите мы выбираемCountry а нужно, чтобы кавычками как текстовоеviktturДля блок1: нули. Как их читайте в статье с помощью формулы.можно корректировать формулами вас актуальными справочными

Извлекаем имя: извлекать левую часть(НАЙТИ), этого инструмента Вы если Вам нужно желаемый формат вSpaceи они были помещены значение).: Формулы. ради интереса=ПСТР(ПОДСТАВИТЬ(A4;»*»;»»);ПОИСК(«-«;A4)+2;15) убрать, скрыть, заменить, «В Excel по У нас такой – имя и

материалами на вашем=LEFT(A2,FIND(» «,A2,1)-1) текста из ячейкиSEARCH можете взять какую-то переместить границу столбца разделе(Пробел) иArrival Date в разных столбцах.Для объединения текстов в домучил определение блок2В конце строки читайте в статье

Как в Excel разбивать ячейки при помощи формул

имени определить пол». список. отчество заменить на языке. Эта страница=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)A2(ПОИСК) и часть данных, находящихся в другое место,Column data formatComma.Если кто-то забыл, я Во-вторых, при разбиении одну строку программа и блок6. перед знаком «*» «Как убрать нулиВыделяем ячейки, текстНам нужно перед фамилиями инициалы, соединить слова переведена автоматически, поэтомуИзвлекаем фамилию:вплоть до позицииLEN в одном или просто перетащите вертикальную(Формат данных столбца).(Запятая), а также напомню быстрый способ

Пример 1

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

=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(« первого найденного пробела.(ДЛСТР). Далее в нескольких столбцах, и

линию мышью. НаНа этом же шаге
ставим галочку напротив
вставить сразу несколько
чтобы получить возможность функцию
и без; "*"
а может и

Khlopkov разделить или весь В ячейке нового предложение, вставить слова

 содержать неточности и «,A2,1)+1))=LEFT(A2,SEARCH(» «,A2)-1) этом разделе я ввести их в самом деле, все мастера Вы можете параметра столбцов на лист

качественнее настроить работу
=СЦЕПИТЬ(A2;" ";B2), которая

— то оно не быть, поэтому,: Здравствуйте, столбец. столбца пишем формулу. в список Excel. грамматические ошибки. Для=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«=ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1) кратко объясню назначение новый столбец. Думаю, эти инструкции подробно выбрать, в какойTreat consecutive delimiters as Excel. Для этого фильтра, сортировку или объединяет текстовые значения есть, то нет,

 если нужно удалитьПодскажите, есть такаяЗаходим на закладке «Данные»=»Квартиросъемщик»&» «&A8У нас есть нас важно, чтобы «;A2;1)+1))LEN каждой из этих

Вы лучше поймёте
расписаны в верхней

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

в раздел «Работа
Копируем эту формулу

таблица, где фамилия, эта статья былаИзвлекаем суффикс:(ДЛСТР) – считает функций и приведу о чём я части диалогового окна данные. Для этого(Считать последовательные разделителиStatus

анализа.
Однако на практике

 блок6 то с блок1 необходимо дорастить: по столбцам с с данными» и вниз по столбцу. имя и отчество

вам полезна. Просим
=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))

длину строки, то примеры, которые Вы говорю из следующегоТак как каждый ID

кликните по иконке
одним). Этот параметр

, кликнув по егоРазбиваем ячейки при помощи мы не рекомендуем пробелом после числа,=СЖПРОБЕЛЫ(ПСТР(…)) разделителями (Данные текст выбираем функцию «Текст Получился такой список.

Пример 2

написаны в разных вас уделить пару=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)) есть количество символов сможете использовать в примера.

	товара содержит 9	выбора диапазона (в	поможет избежать лишнего	заголовку, и, удерживая
инструмента «Текст по	использовать функцию СЦЕПИТЬ,	то без, не	Труднее с блок2	по столбцам), можно
по столбцам». В	Первый столбец можно скрыть	ячейках. Нам нужно	секунд и сообщить,	А вот формулы, позволяющие

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

ли формулой сделать появившемся окне «Мастер или скопировать значение разместить их в помогла ли она разбить имена с

Синтаксис формулы:
Самая распространённая ситуация, когда

инструмент «Мгновенное заполнение» границы столбца на иконка называется когда между словами мыши, протащите указательКак разбить объединённые ячейки

Пример 3

нее просто применять и т.д. условия задачи больше то же самое?

	текстов», в строке	нового столбца без	одной ячейке. Вручную	вам, с помощью
фамилией, стоящей впереди	=LEN(text)	могут понадобится эти	включен. Вы найдёте	это значение, как
Свернуть диалоговое окно	есть 2 или	вправо, чтобы выделить	в Excel	в формулах оператор

Практического применения почти
не будут меняться
Пример прилагаю
«Формат исходных данных»
формул, а первый переписывать список долго.
кнопок внизу страницы. и отделенной от
=ДЛСТР(текст)
формулы – это
этот параметр на

Пример 4

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

	(т.е. расположение в	Serge	указываем – «С	столбец и второй
Но, в таблице	Для удобства также	имени запятой, и	Следующая формула считает количество	необходимость разделить имена
вкладке	выше.	Destination	Настраиваем ограничитель строк	(сколько хотите вставить).

2013 при помощи
она имеет ряд такие формулы нужно
других строках структурно: Здравствуйте.
разделителями». Нажимаем кнопку
с формулами -
Excel, есть специальная
приводим ссылку на
отчеством, находящимся в
символов в ячейке

Пример 5

из одного столбцаFileНа следующем шаге выберите(Поместить в) и. Этот параметр может Затем кликните правой инструмента «Мгновенное заполнение» своих недостатков. писать под конкретную похоже на показанное34,5 Кб «Далее». удалить.

	функция. Есть два	оригинал (на английском	конце:	A2
по нескольким. На	(Файл) >	формат данных и	выберите крайний левый	понадобиться, если в
кнопкой мыши по	Формулы для разбиения столбцов		структуру данных. Какие-нибудь	в примере), можно

Khlopkov
Здесь указываем в строке
Ещё один способ
варианта.
языке) .
A
:
рисунке ниже показано,
Options

укажите ячейки, куда столбец из тех, столбце, который Вы выделенной области и (имен и других3 главных недостатка функции

мелкие изменения данных сочинить чего-нибудь на
: Спасибо )))
«Символом – разделителем

добавить текст, числа,

office-guru.ru

Распределение содержимого ячейки на соседние столбцы

Первый вариант.Можно разделить содержимого ячейкиB=LEN(A2) какого результата мы(Параметры) > поместить результат, как в которые Вы разбиваете, содержатся какие-либо в контекстном меню текстовых данных) СЦЕПИТЬ по сравнению повлекут неработоспособность формул досуге.vikttur является» — «пробел». символ к текстуУ нас такой и распространение составныеC=ДЛСТР(A2) пытаемся достичь:

Advanced это было сделано хотите поместить разделённые значения, заключённые в выберите командуИнструмент « с оператором &: блок2 и блок6.Казанский: Вариант: Поставили «пробел», потому в ячейке, смотрите список.

 части нескольких смежныхD

Если имена в ВашейВы легко сможете разбить(Дополнительно) > в предыдущем примере, данные. К сожалению, кавычки или в
InsertТекст по столбцамОграниченное максимальное количество объединяемых

Vanya7819

: Попробуйте. Можно еще=ПСТР(B2&» №»;1;ПОИСК(» №»;B2&» что в нашем в статье «ДобавитьНам нужно написать в ячеек. Например, если1 таблице содержат отчества такие имена наAutomatically Flash Fill а затем нажмите невозможно импортировать разделённые апострофы, и Вы(Вставить).» действительно очень удобен, значений ячеек до: Спасибо!!! доделать ограничение второй №»)-1) списке имя и текст в ячейки

ячейке D1 ФИО лист содержитПолное имя или суффиксы, то
два столбца при(Автоматически выполнять мгновенноеFinish данные на другой хотите, чтобы такиеРезультат будет примерно таким, когда нужно разделить
255.У меня ещё подстроки в 3=ЕСЛИ(E2=B2;»»;ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2)) фамилия написаны через

 с текстом Excel». одним предложением. ПишемПолное имяИмя потребуются немного более помощи следующих формул: заполнение).(Готово). лист или в участки текста не что Вы видите

support.office.com

Объединить слова из разных ячеек в текст Excel.

 данные из одногоФункция объединяет только текстовые такой вопрос, может слова, т.е.Khlopkov пробел. Если быС помощью формул в этой ячейке
столбца, можно разделить столбецОтчество сложные формулы сИзвлекаем имя (столбец FirstТеперь давайте посмотрим, какЕсли Вы объединили несколько другую рабочую книгу, разбивались, а рассматривались на рисунке ниже столбца по нескольким значения, а оператор глупый ))), а
METEO GRIP E+
: Да работает, но имя и фамилия
можно преобразовать список, (D1) формулу. Нажимаем на двух столбцовФамилия использованием функции name): можно автоматически разбить
ячеек на листе попытка сделать это как цельные значения. (новые столбцы вставлены в Excel 2013, & объединит любые ВПР, в данной TL -> METEO есть но.. не (др. слова) были
где имя, отчество на ячейку (активной —2MID=LEFT(A2,SEARCH(» «,A2,1)-1) данные по ячейкам. Excel и теперь
приведёт к сообщению
Например, если Вы
слева от выделенных 2010, 2007 или
 значения ячеек.
ситуации, ничем не GRIP E+ получается вытащить из написаны через запятую,
и фамилия написаны
сделаем).имениWhite, David Mark(ПСТР).=ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)-1) Итак, Вы включили
хотите вновь разбить об ошибке выбора
выберите в качестве столбцов): 2003.Функция требует немного больше поможет?Vanya7819
текста число если то мы бы полностью, вЗаходим на закладкеиDavid
Вот такие формулы нужноИзвлекаем фамилию (столбец Last инструмент «Мгновенное заполнение»,
их по отдельным конечной ссылки. разделителя запятую, аПримечание:
«Текст по столбцам» позволяет
затрат времени иvikttur
: Виктор, Спасибо за к примеру указали разделителем «запятая».список с фамилией и «Формулы» в разделФамилииMark использовать, когда имена, name):
и начинаете вводить столбцам, откройте вкладкуСовет: в качестве ограничителя
Если у Вас
разбивать значения ячеек, ресурсов как у: Слабо представляю. Почему
ответ! Блок2 и0,02 г №50В этом окне, в инициалами «Библиотека функций», выбираемстолбцом.White
которые требуется разбить,=RIGHT(A2,LEN(A2)-SEARCH(» «,A2,1)) с клавиатуры данные,HomeЕсли Вы не строк – кавычки нет столбцов, следующих
отделённые разделителями, или программы Excel (в ВПР, а не, блок6 в основном табл разделе «Образец разбора. Например, в ячейке – «Текстовые», иСоветы:
Извлекаем имя: содержат отчество или
=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2;1)) которые нужно поместить
(Главная) и в
хотите импортировать какой-то («), тогда любые непосредственно за тем, выделять данные фиксированной процессе вычислений с скажем, СЛУЧМЕЖДУ? :) будет, как в25 мг №20упак данных» сразу видно, написано. выбираем функцию «СЦЕПИТЬ». =MID(A2,SEARCH(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(« только один инициалДля тех, кому интересно, в отдельные ячейки.
группе команд столбец (столбцы), который слова, заключённые в что Вы хотите ширины (когда все использованием сложных формул),Одна из самых популярных примере. Если уSerge какие столбцы уВ соседнем столбце пишем В появившемся окнеСведения об альтернативном способе «,A2,1)+1)) отчества посередине. что означают эти По мере вводаAlignment

excel-office.ru

Разделить текст Excel на несколько ячеек.

показан в области кавычки (например, «California, разбить, то необходимость значения содержат определённое так и у операций в Exel ВАС будет время: Ну так рисуйте нас получатся. Нажимаем
такую формулу. указываем адреса ячеек, распределения текста по=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(«A формулы, я попробую Excel будет пытаться(Выравнивание) нажмите маленькуюData preview USA»), будут помещены в этом шаге количество символов). Давайте пользователя (в процессе по работе с
— напишите ещё пример согласно правилам кнопку «Далее».=СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1));ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1)+1;1);».»;ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(«
которые нам нужно столбцам см. в «;A2;1)+1))B объяснить более подробно. распознать шаблон в чёрную стрелку рядом(Образец разбора данных), в одну ячейку. отпадает и его рассмотрим эти варианты
ввода функции в текстом – это формулы для них. — будет решение.Здесь, в разделе «;СЖПРОБЕЛЫ(A1);НАЙТИ(» «;СЖПРОБЕЛЫ(A1);1)+1)+1;1);».») объединить в одно статье Разделение текстаИзвлекаем отчество:CSEARCH вводимых значениях, и с кнопкой то выделите его Если же в можно пропустить. Главное подробнее: строку формул). объединение текстовых строк.
Они могут бытьKhlopkov «Поместить в:» указываемПолучилось. предложение. Получилось так.  по столбцам с=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))D(ПОИСК) или как только онMerge & Center и выберите вариант качестве ограничителя строк не упустите, чтоКак разбить текст сИнтересный факт! В Excel Например, объединение нескольких любой сложности, только
: Простите, что сразу диапазон столбцов, вЕсли между словамиФИО написаны без пробелов. помощью функций.=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))1FIND
его распознает, данные(Объединить и поместить
Do not import column установить значение пустых столбцов должно разделителями по столбцам нет обратной функции слов в одну чтоб был желаемый

такого варианта не которые поместятся новые появились лишние пробелы, Чтобы это исправить,Можно объединить ячейки, содержащиеИзвлекаем фамилию:Полное имя(НАЙТИ) – это автоматически будут вставлены в центре). Далее
(Пропустить столбец) вNone быть не меньше,Как выделить текстовые данные РАСЦЕПИТЬ и нет строку. На практике результат. Ещё раз было в примере.
данные. их можно удалить. формулу нужно доработать. функцию СЦЕП или=LEFT(A2,FIND(» «,A2,1)-2)Имя абсолютно идентичные функции, в остальные ячейки.

excel-office.ru

Текст по столбцам формулой

 из выпадающего списка разделе
(Нет), тогда слово чем количество столбцов, фиксированной величины специального символа оператора. в процессе рутинной Спасибо!!!vikttur
Внимание!

 Подробнее о б Между адресами ячеек

функцию СЦЕПИТЬ.

=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-2)Отчество

 которые выполняют поиск Чтобы понять, как
выберитеColumn data format
«California» будет помещено

 на которое выПредположим, есть список участников, Для решения такой работы с ExcelVanya7819: Моя первая работает

Столбцы этого диапазона этом читайте в
после точки с

Выполните следующие действия.Как Вы понимаете, этиФамилия позиции определенной текстовой

 это работает, посмотритеUnmerge Cells(Формат данных столбца). в один столбец,

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

 запятой написатьПримечание:
формулы работают не2 строки в заданной на рисунок ниже:
(Отменить объединение ячеек).Нажмите а «USA» –Выделите столбец, который требуется

 или какое-то другое с комбинациями текстовых Имена и Фамилии Спасибо!!! Только я подправил, не вдаваясь иначе информация в лишние пробелы в

» « Диапазон, содержащий столбец, который только для разделенияSally K. Brooke
ячейке. Синтаксис формулы:

Как видите, я ввёлТаким образом объединение ячеекFinish в другой. разбить. Затем откройте
мероприятие. На рисунке

 функций. Например маркером сотрудников в одну не совсем понял в подробности: них сотрется и Excel». Этими же
. Получилась такая формула. вы хотите разделить
имён в Excel.
Sally
=SEARCH(find_text,within_text,[start_num]) только пару имён будет отменено, но(Готово)!
В нижней части диалогового
вкладку
ниже видно, что
для расцепления текста ячейку. ВАШ ответ. Результат=ЕСЛИ(E2=B2;»»;ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2)) заменится на новую. способами можно удалить=СЦЕПИТЬ(A1;» «;B1;» «;C1) может включать любое
Вы можете использовать
K.=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция]) в столбец удовольствие от результатаЕсли данные состоят из окна находится областьData в столбце послужит символ пробелаВ примере показанным на

 то что надо,ПРАВСИМВ(…;1;2) — извлекает Если нет пустых пробелы между числами,Получилось так.
количество строк, но их для разбиенияBrooke

В качестве аргументов ВыB будет испорчено тем, текстовых или числовыхData preview(Данные) >Participant « » , рисунке таблица содержит только как это только два знака. столбцов, можно предварительно в формуле, т.к.Теперь формулу копируем вниз

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

Data Tools(Участник) перечислены имена а дальше: первые два столбца:

 работает?Khlopkov вставить столбцы. Как лишние пробелы могут по столбцу.
не более одного одного столбца по=LEFT(A2,FIND(» «,A2,1)-1) нужно найти, где

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

чтобы расцепить только имяИмя – ячейки вКазанский: Работает!! Спасибо! это сделать, смотрите

 привести к ошибкеВторой вариант. столбца. Это важные нескольким. Например, следующие=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1) нужно искать, а ячейки именами из столбце. Думаю, Вы можете разбить ихNext > ожидаемая дата прибытия: используем формулу: =ЛЕВСИМВ(C2;ПОИСК(« этой колонке содержат: Это функция, написаннаяНу раз уж в статье «Как при подсчете или

Вместо функции «СЦЕПИТЬ» из них достаточно формулы Вы можетеИзвлекаем отчество:
также позицию символа, столбца догадались, что нужно на несколько столбцов(Далее) будет разумнымText to ColumnsНеобходимо разбить этот текст «;C2;1)) текстовые данные полученные на VBA. См.
как говориться просить добавить строку, столбец формула не будет можно просто нажать пустыми столбцами справа использовать, чтобы разбить=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(« с которого следует

A снова использовать функцию
следующим способом. пролистать это поле(Текст по столбцам). на отдельные столбцы,чтобы расцепить только фамилию из базы картотек

 Alt+F11 о помощи до в Excel» тут. считать.

planetaexcel.ru

Лучшая альтернатива функции СЦЕПИТЬ и объединить текст в Excel

кнопку амперсанд (&). от выбранных столбец, текстовые данные, разделённые «,A2,1)+1)) начать поиск. В. Если вы довольныText to ColumnsК примеру, есть список и убедиться, чтоОткроется диалоговое окно чтобы таблица имела – формула: =ПРАВСИМВ(C4;ПОИСК(« кадрового отдела.vikttur

Как сцепить два текста в один с помощью и без функции Excel?

до конца))Можно указать форматМожно данные в

Формула получится такая. который будет предотвратить запятыми:=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(« нашем примере
результатом, просто нажмите(Текст по столбцам), товаров с ID

Excel правильно распределилConvert Text to Columns следующие данные (слева «;C4;1)+1)Фамилия – в данном: Строка 8: «…85А если нет Ячеек столбцов. Например,

строке переместить из=A2&B2&С1 перезапись по данные,A

«;A2;1)+1))SEARCH(» «,A2,1)Enter чтобы разбить данные и наименованием, причем все данные по wizard направо):Фактически функция СЦЕПИТЬ устарела,

столбце находиться только T XL - № то вместо если вы делите последних ячеек вРезультат такой же, которые распространяться данныеBИзвлекаем фамилию:или, и весь столбец из одного столбца идентификатор товара – столбцам.(Мастер распределения текста

First Name но все еще фамилии сотрудников. Marangoni *», блок6=»85
пустого значения, значение столбец на дни первые, как в первом в любой смежныхC=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))ПОИСК(» «;A2;1) будет заполнен именами. на два или это 9 символов,Осталось сделать всего две

по столбцам). На

(Имя), доступна пользователям вВ третьем столбце «Имя

T XL» =1 и месяцы, год,
перевернуть строку варианте. Если нет столбцах. При необходимостиD
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))говорит о том, Очень умный инструмент, более столбцов. которые стоят перед вещи – выбрать первом шаге мастераLast Name новых версиях программы + Фамилия» необходимо

Строка 7: «…Khlopkov то можно указать. Например, в ячейках пропусков между словами, вставьте количество пустые1Функция что мы хотим не правда ли?Если Вы уже обновились наименованием этого товара:

формат данных и Вы выбираете формат(Фамилия),
Excel. объединить имя и TL 79Q -

: Блин! Всего не «Формат данных столбца» написано: в первой то вставьте пробел столбцы, будет достаточно

exceltable.com

Полное обозначение

Источник

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

ФИО в одном столбце (а надо — в трех отдельных, чтобы удобнее было сортировать и фильтровать)
полное описание товара в одном столбце (а надо — отдельный столбец под фирму-изготовителя, отдельный — под модель для построения, например, сводной таблицы)
весь адрес в одном столбце (а надо — отдельно индекс, отдельно — город, отдельно — улица и дом)
и т.д.

Поехали..

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns). Появится окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой
внутри названия.

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

общий — оставит данные как есть — подходит в большинстве случаев
дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
текстовый — этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor)
в Excel 2007 и новее — вкладка Разработчик — Редактор Visual Basic (Developer — Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String
Dim x As Variant
    x = Split(Txt, Delimiter)
    If n > 0 And n - 1 <= UBound(x) Then
        Substring = x(n - 1)
    Else
        Substring = ""
    End If
End Function

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

где

Txt — адрес ячейки с текстом, который делим
Delimeter — символ-разделитель (пробел, запятая и т.д.)
n — порядковый номер извлекаемого фрагмента

Например:

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Function CutWords(Txt As Range) As String
    Dim Out$
    If Len(Txt) = 0 Then Exit Function
    Out = Mid(Txt, 1, 1)
    
    For i = 2 To Len(Txt)
        If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then
            Out = Out & Mid(Txt, i, 1) & " "
        Else
            Out = Out & Mid(Txt, i, 1)
        End If
    Next i
    CutWords = Out
End Function

Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:

Ссылки по теме

Деление текста при помощи готовой функции надстройки PLEX
Что такое макросы, куда вставлять код макроса, как их использовать

Источник

Проверьте, как это работает!

См. также

Нужна дополнительная помощь?

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

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

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

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

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

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

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

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

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

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

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

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

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

Как использовать функцию «Текст по столбцам»

Деление строки

Делим электронную почту на имя пользователя и домен

Получаем главный адрес сайта из ссылки

Приведение дат к общему формату

Смена формата с текстового на числовой

Убираем несколько первых символов значения каждой ячейки

Преобразуем числа со знаком минус на конце в отрицательные числа

Как выглядит склеенный текст в Excel

Разделение текста на столбцы в Excel

Необходимо разделить ФИО по отдельным столбцам

Разделение текста с помощью формулы

Этап №1. Переносим фамилии

Этап №2. Переносим имена

Этап №3. Ставим Отчество

Заключение

Способ 1: Использование автоматического инструмента

Способ 2: Создание формулы разделения текста

Шаг 1: Разделение первого слова

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

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

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

Разбиваем текст фиксированной ширины по нескольким столбцам

Разбиваем объединённые ячейки в Excel

Разделяем данные на несколько столбцов в Excel 2013 при помощи мгновенного заполнения

Как в Excel разбивать ячейки при помощи формул

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Распределение содержимого ячейки на соседние столбцы

Объединить слова из разных ячеек в текст Excel.

Разделить текст Excel на несколько ячеек.

Текст по столбцам формулой

Лучшая альтернатива функции СЦЕПИТЬ и объединить текст в Excel

Как сцепить два текста в один с помощью и без функции Excel?

Делим слипшийся текст на части

Способ 1. Текст по столбцам

Способ 2. Как выдернуть отдельные слова из текста

Способ 3. Разделение слипшегося текста без пробелов

Ссылки по теме

А вот еще интересные статьи:

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