Разбить строку текста в excel

Skip to content

Как в 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. Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:

=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)

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

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

=ПСТР(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«;A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)

Логику работы ПСТР мы рассмотрели чуть выше.

  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 быстрых шага:

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

  1. Панель Разделить текст откроется в правой части окна 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 с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…

Источник

Содержание

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

Как расцепить текст в Excel

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

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

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

    2. Выбор текста для быстрого разделения при помощи встроенного инструмента Excel

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

    3. Переход к инструменту быстрого разделения текста в Excel

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

    4. Выбор типа автоматического разделения текста в Excel

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

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

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

    6. Просмотр предварительного результата автоматического расцепления текста в Excel

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

    7. Результат автоматического расцепления текста в Excel

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

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

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

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

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

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

    2. Создание вспомогательных столбцов для ручного разделения текста в Excel

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

    3. Создание первой формулы для разделения первого слова из текста в Excel

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

    4. Переход к редактированию аргументов функции разделения первого слова текста в Excel

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

    5. Выбор ячейки с текстом для разделения первого слова в Excel

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

    6. Создание функции поиска для нахождения пробела в первом слове при его разделении в Excel

    Lumpics.ru

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

    7. Переход к редактированию аргументов функции поиск при разделении первого слова в Excel

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

    8. Выбор текста для поиска первого пробела при разделении слова в Excel

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

    9. Редактирование формулы ЛЕВСИМВ для отображения первого слова при разделении текста в Excel

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

    10. Возвращение к таблице для проверки отображения первого слова при разделении в Excel

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

    11. Растягивание формулы после разделения первого слова в Excel

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

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

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

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

    2. Создание формулы для разделения второго слова в Excel

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

    3. Выбор ячейки при поиске строки для разделения второго слова в Excel

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

    4. Создание функции ПОИСК для поиска начальной позиции при разделении второго слова в Excel

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

    5. Настройка функции ПОИСК для поиска начальной позиции при разделении второго слова в Excel

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

    6. Редактирование функции для учета пробела при настройке формулы разделения второго слова в Excel

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

    7. Переход к настройке функции поиска второго пробела при разделении слова в Excel

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

    8. Настройка функции поиска второго пробела при разделении слова в Excel

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

    9. Создание вспомогательной функции для поиска второго пробела в Excel

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

    10. Настройка вспомогательной функции для поиска второго пробела в Excel

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

    11. Редактирование первой функции ПОИСК для второго слова при разделении в Excel

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

    12. Завершающий этап настройки формулы для разделения второго слова в Excel

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

    13. Добавление последнего выражения для формулы разделения второго слова Excel

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

    14. Результат работы формулы для разделения второго слова в Excel

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

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

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

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

    2. Переход к настройке формулы для разделения третьего слова в Excel

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

    3. Выбор ячейки для разделения третьего слова в Excel

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

    4. Создание функции ДЛСТР для поиска количества символов в строке при разделении слова в Excel

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

    5. Добавление функции ПОИСК для разделения третьего слова в Excel

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

    6. Стандартная настройка функции ПОИСК для разделения третьего слова

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

    7. Добавление начальной позиции для функции ПОИСК при разделении третьего слова в Excel

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

    8. Настройка начальной позиции для функции ПОИСК при разделении третьего слова в Excel

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

    9. Переход к предыдущей функции ПОИСК для завершения настройки разделения третьего слова в Excel

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

    10. Настройка начальной позиции для завершения разделения третьего слова в Excel

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

    11. Проверка разделения третьего слова при работе формулы в Excel

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

    12. Результат разделения всех трех слов в Excel

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

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

Источник

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

​Смотрите также​ без цитирования обойтись…​: Отлично, спасибо большое.​Юрий М​ перевести число и​

  1. ​ ее отображение.​ обладают функционалом простейшего​1 Из строки формул.​

  2. ​ обозначить мой разделитель​​ arrTmp1(j) .Cells(lCnt, 3)​​ Explicit Sub jjj_split()​​ в файл и​​: В файле на​

  3. ​ ячейки, кликаете​​, чтобы указать способ​ распределения текста по​​ листе, где должны​​С помощью мастера распределения​​mer1k​​ А теперь последний​​: В примере была​

  4. ​ сумму прописью в​​В режиме редактирования работают​​ текстового редактора. Главное​ Щелкните по ячейке,​​ )))​​ = arrTmp2(j) End​​ Dim awsh As​​ просто запускайте макрос.​ копии листа1 давите​​Текст по столбцам​​ разбиения текста на​

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

  6. ​: Ну да, цитаты​​ вопросик думаю. Как​​ одна строка и​ Excel.​ все стандартные комбинации​

  7. ​ отличие – это​​ в которой необходимо​​…..надеюсь понятно написала​ With ‘ wshResult​ Worksheet Dim wshResult​

  8. ​ Или просто -​​ кнопку с побудительной​​и указываете в​

support.office.com

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

​ разных столбцах.​​ статье Разделение текста​Нажмите кнопку​ текст, содержащийся в​ с вложениями, это​ мне такое провести​ указано место -​mer1k​ клавиш управления курсором​ разбиение текста на​ отредактировать данные. В​ ))​ Next j Next​ As Worksheet Dim​ откройте файл моего​ надписью и проверяйте​ качестве разделителей Запятую​Примечание:​ по столбцам с​Готово​ одной ячейке, можно​ беспорядочно конечно…Будем знать)​

​ с 12000 строками?​ куда выгружать результат.​: Добрый день. Скажите,​ клавиатуры, как и​ строки.​​ строке формул отобразиться​​Vlad999​ i End Sub​ arrDataIn Dim i​​ примера и свой​​ результат.​​ и пробел. Ячейку​​ Для получения справки по​

​ помощью функций.​​.​

  • ​ разделить на несколько.​ В любом случае​ Мне ж получается​ Если исходных строк​ каким образом можно​ в других программах​

  • ​Внимание!​ содержимое, которое доступно​: СИМВОЛ(10) и есть​

​mts2050​

​ As Long Dim​​ — и запускайте​Voditel_CDROMa​ вывода я указал​ заполнению все действия​Можно объединить ячейки, содержащие​Примечание:​Выделите ячейку или столбец​ Спасибо Вам)​ нужно изменить макрос?​ несколько — куда​ разбить текст одной​ Windows:​Чтобы разбить текст​ для редактирования. Сделайте​ этот разделитель.​: Спасибо.​ n As Long​ макрос через Alt+F8/​: Как вариант с​ B6 (Лист2)​

  1. ​ мастера читайте статью​ функцию СЦЕП или​Мы стараемся как​ с текстом, который​

  2. ​vikttur​​ Только как правильно?​​ выгружать результат? Может​​ ячейки, на строки?​​Клавиша «DELETE» удаляет символ​​ на строки в​​ необходимые изменения, после​

    Группа

  3. ​не совсем понял​​Jack Famous​​ Dim arrTmp1$(), arrTmp2$()​ Макрос-то делался под​ помощью функции SubStr(ссылка​

    ​А в итоговой​​ Разделение текста по​ функцию СЦЕПИТЬ.​ можно оперативнее обеспечивать​ вы хотите разделить.​: Знайте. И желательно​Юрий М​ в соседний столбец?​​ Поиск юзал, но​ изображение кнопки​ справа, а «Backspace»​ одной ячейке Excel​

support.office.com

Помогите текст разбить на ячейки по строкам

​ чего нажмите Enter​​ вопрос. Приложите файл​: irakli78, mts2050, доброго​ Dim n2 As​ данные из примера.​ на строку, номер​ таблице я записал​ различным столбцам с​Выполните следующие действия.​
​ вас актуальными справочными​:)
​На вкладке​:)

​ пройтись и исправить,​​: А Вы пробовали​mer1k​ нашел только способ​ слева.​ нужно нажать комбинацию​ или кликните по​ пример, так есть,​ времени суток! Недавно​ Long Dim j​А если там​ фрагмента, разделитель). См.​
​ в ячейках ссылки​ преобразовать мастера текстов,​Примечание:​ материалами на вашем​Данные​
​ чтобы не грузить​ просто добавить строки​​: Мне нужно именно​​ для разделения ячейки​CTRL+ «стрелка влево» переход​ клавиш Alt+Enter. Тогда​ кнопке «Ввод», которая​ так хотелось бы​
​ мне помогли с​ As Long Dim​ отличия какие -​ в приложении.​ на ти самые​
​ или нажмите кнопку​

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

​ и нажать на​​ раскидать текст на​ на СТОЛБЦЫ. За​ на начало слова,​

​ вы сможете перейти​​ размещена в начале​ получить сделанный вручную.​ такой же проблемой.​ lCnt As Long​
​ надо смотреть и​irakli78​ B6 и т.д.​Справка​ вы хотите разделить​

​ переведена автоматически, поэтому​​Текст по столбцам​Login_excel​ кнопку?​ строки, чтоб и​ ранее спасибо. Пример​ а CTRL+ «стрелка​

​ на новую строку​​ строки формул. Для​Фишка​Универсальное решение.​

​ Set awsh =​​ макрос корректировать, быть​: Просто не хватает​Подойдёт так, Ираклий?​в мастере разделения​ может включать любое​

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

​ вправо» – в​​ в ячейке. В​ отмены изменений можно​: Ну как-то так​А как заполнить​ ActiveSheet With awsh​ может.​:)​ слов благодарности!! Честно​Юрий М​ текста столбцов.​

​ количество строк, но​​ содержать неточности и​В​ разнести многострочный текст​ меня цитировать, да​

​ посмотреть количество номеров.​​ 2007​ конец слова.​ том месте текста,​
​ нажать клавишу «Esc»​ ))​ пустые ячейки значениями​

​ arrDataIn = .Range(«A2:C»​​AVKL​ говоря пока не​: wowik7, количество переносов​irakli78​ он может содержать​ грамматические ошибки. Для​мастере распределения текста по​ по отдельным строкам​ ещё и целиком.​Я пробовал разбить​Smiley​«HOME» смещает курсор клавиатуры​ где находится курсор​ или кнопку «Отмена»​Фишка​ из верхних, узнаете​ & .Cells(.Rows.CountLarge, 1).End(xlUp).Row).Value​: Добрый день Пытливый!​
​ проверял, зашел с​ можно узнать программно​: Добрый день! Буду​ не более одного​ нас важно, чтобы​

​ столбцам​​ из одной ячейки?​
​ Зачем?!!​ эти данные на​: mer1k, разбейте его​

​ в начало строки,​​ клавиатуры, появится перенос​ (возле кнопки «Ввод»).​: ой, забыла написать,​ (готовый макрос в​ .Parent.Sheets.Add , awsh,​Спасибо большое за​ телефона… но все​

​ ))​​ очень благодарен, если​ столбца. Это важные​
​ эта статья была​установите переключатель​Bema​

​mer1k​​ столбцы. Т.е. текст​ на столбцы с​

​ а «END» –​​ строки и соответственно​2 Из самой ячейки.​ как надо ))​

​ комментариях)​​ 1, xlWorksheet End​ решение! Очень помогло​ равно спасибо!!​Юрий М​ кто-нибудь посоветует как​ из них достаточно​ вам полезна. Просим​с разделителями​: Привет.​: Прошу прощения. Просто​ разбивается в своей​ помощью «текст по​ в конец.​ начало новой.​ Перейдите на ячейку​Vlad999​Фишка​ With ‘ awsh​ в работе!​) завтра посмотрю и​: irakli78, зачем Вам​ можно разбить текст​ пустыми столбцами справа​ вас уделить пару​и нажмите кнопку​1. Формулой​ в предыдущем примере,​ же строке, только​ столбцам» и потом​Если в тексте более​В обычных редакторах текст​ и нажмите клавишу​: Решение макросом в​: Необходимо разбить текст​ Set wshResult =​mts2050​ обязательно сообщу! Еще​ объединённые ячейки? Настрадаетесь​ который в одной​ от выбранных столбец,​ секунд и сообщить,​Далее​2. Макросом​ пробовал. не получалось.​ на столбцы. Но​ транспонируйте.​ одной строки то​ делится на строки​ F2 или сделайте​ теме: Разбить текст​

​ в ячейке (с​​ ActiveSheet n =​

​: Подскажите как сделать​​ раз спасибо!​ потом…​ ячейке на несколько​ который будет предотвратить​​ помогла ли она​
​.​3. Текст по​ Сейчас сделал, и​ как потом с​Сергей​;)

planetaexcel.ru

Разбить текст ячейки (строки), содержащий разделитель, на строки

​ комбинации CTRL+HOME и​​ нажатием клавиши Enter,​ по ней двойной​ ячейки (строки), содержащий​ разделителями «Alt+Enter») на​

​ UBound(arrDataIn, 1) lCnt​​ что бы текст​irakli78​Все_просто​ строк с соблюдением​ перезапись по данные,​ вам, с помощью​

​Выберите​​ столбцам.​ все хорошо. Огромное​ этих столбцов разместить​: можно так​ CTRL+END перемещают курсор​ но в Excel​ щелчок мышкой. Тогда​
​ разделитель, на строки​ отдельные строки​ = 0 For​ разбивался не в​: Хотел бы узнать,​: Человек по сути​ некоторых параметров…​
​ которые распространяться данные​ кнопок внизу страницы.​

​разделители​​alexleoix​ спасибо, оочень упростили​
​ номера в строки,​Юрий М​ в начало или​ данное действие выполняет​ в ячейке появится​

​ макросом​​Genbor​ i = 1​

​ одной ячейке, а​​ а как данный​ просит написать макрос,​

​Пример прилагаю​​ в любой смежных​ Для удобства также​для своих данных.​: 4. Power Query​ задачу.​

CyberForum.ru

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

​ причем за раз?​: Или так.​ в конец целого​ функцию подтверждения вода​ курсор клавиатуры, а​Если ячейка содержит большой​: Код =ПОДСТАВИТЬ(A1;»»;СИМВОЛ(10)) -​ To n arrTmp1​

​ в нескольких и​ макрос использовать уже​ а вы на​Заранее благодарю!!!​ столбцах. При необходимости​ приводим ссылку на​ Например,​БМВ​По поводу цитирования.​

Редактирование строки текста в ячейках

​ Можно конечно взять​mer1k​

  1. ​ текста.​ данных и переход​ ее размер изменится​ текст или сложную​ это символ, на​ = Split(arrDataIn(i, 2)​ соотносился между ними.​ в другом файле?​ него взъелись.​wowik7​ вставьте количество пустые​ оригинал (на английском​запятую​: 5. Word (​ А что тут​ и скопировать строку​: Спасибо. Как этим​Строка формул.
  2. ​Примечание. Точно так же​ на следующую ячейку.​ на время редактирования.​ формулу, но с​ основании которого будет​ & «|», «|»)​ Пример в файле.​Hugo​Я, к сожалению,​: Скажите, а известно,​ столбцы, будет достаточно​ языке) .​и​ и это не​ такого? Именно для​ в первый столбец​ пользоваться? Добавлять туда​

Редактирование текста в ячейке.

​ редактируются: формулы, функции,​ Поэтому как написать​ После всех изменений​ ошибками, то нет​ переноситься строка (запятая,​ arrTmp2 = Split(arrDataIn(i,​JayBhagavan​

​: Описка, исправьте в​

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

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

​ этого кнопочка и​
​ путем транспонирования. С​ свой текст?​ числа, даты и​ несколько строк в​ нажмите Enter или​ смысла удалять их​ точка с запятой,​ 3) & «|»,​: mts2050, и Вам​ irakli78.xls (45.5 КБ)​ в макросах. Хотя​ в строке? Вот​ составные части распределенных​

​ и распространение составные​. Данные можно предварительно​ в word. копируем,​ создана. Всю жизнь​ одной строкой то​Попробовал свое ввести,​ логические значения.​ ячейке Excel жмите​ Tab или кликните​ вводить все данные​ точка и т.д.)​ «|») n2 =​

Разделение текста на строки.

​ здравия.​ F на G:​ сейчас все равно​ в приложенной книге​ данных.​ части нескольких смежных​ просмотреть в окне​ вставляем в excel​ цитирую​ удается, но вот​ но в строки​Стоит еще отметить, что​ Alt+Enter.​

Перенос по словам.

Режим редактирования

​ мышкой на любую​ заново. Рациональнее их​Фишка​ WorksheetFunction.Max(UBound(arrTmp1, 1) -​Кол-во разделителей в​Range(«G» & lngI​

  1. ​ стараюсь написать.​ 4 слова и​Выделите ячейку, диапазон или​
  2. ​ ячеек. Например, если​Образец разбора данных​ )​Юрий М​ с несколькими никак.​
  3. ​ текст он кидает​ простой режим редактирования​Обратите внимание, что после​ другую ячейку. Для​
  4. ​ просто отредактировать.​: а как в​ 1, UBound(arrTmp2, 1)​ соседних по горизонтали​ & «:G» &​Юрий М​ все разделены запятой​

​ весь столбец, где​ лист содержит​.​vikttur​

​: Цитировать нужно не​ Они раскидывается на​ только с первой​ позволяет задавать тексту​ разделения одной строки​ отмены редактирования нажмите​

Стили текста.

​Для редактирования значений в​ формуле обозначить знак​ — 1) ReDim​ ячейках всегда одинаковое?​ lngI + UBound(strA)).Merge​: Кто «взъелся»? ))​

​ и пробелом. Известно,​ содержатся текстовые значения,​Полное имя​Нажмите кнопку​

exceltable.com

Разделение текста в ячейке, на строки.

​: 6. Ручка +​​ бездумно, а с​ столбцы.​ строки. А как​ свой стиль оформления:​ в ячейке на​ клавишу «Esc».​ Excel предусмотрен специальный​ «разделение», которое ставится​ Preserve arrTmp1(0 To​mts2050​Пытливый​

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

​ умом. И только​​Вот снова скинул​

​ с остальными быть?​​ полужирный, курсив, подчеркнутый​

​ две и более​​Примечание. При редактировании не​ режим. Он максимально​ при помощи клавиш​
​ n2) ReDim Preserve​: Да количество разделителей​: Если структура таблицы​ про объединение ))​ таким же и​На вкладке​ на двух столбцов​.​ клеточку​ то, что ДЕЙСТВИТЕЛЬНО​ табличку. Как например​ У меня этих​ и цвет.​ с помощью клавиш​

​ забывайте о кнопках​​ прост, но гармонично​ Alt+Enter, т.е. перенос​ arrTmp2(0 To n2)​ совпадает.​ такая же (столбцы​ А макрос там​ слов обязательно 4​Данные​

​ —​​Выберите значение в разделе​БМВ​ нужно. Ну вот​ все это разделить,​ строк почти 12000,​
​Обратите внимание, что стиль​ Alt+Enter, то автоматически​ отмены / повтора​ объединил в себе​ на др.строку……..как его​ For j =​JayBhagavan​ и шапка расположены​ не очень и​ ?​в группе​имени​Формат данных столбца​: vikttur, — Почему​ зачем, например, в​ причем уместив только​ в каждой строке​ текста не отображается​ активируется опция «формат​
​ действий на панели​ толь самые полезные​ записать в формулу?​ 0 To n2​: В примере -​ так, как в​

​ сложный…​​Как вариант, метод​

​Работа с данными​​и​или используйте то,​ не в линейку?​ цитате​ в строки в​ бывает и по​ в строке формул,​ ячеек»-«выравнивание»-«перенос по словам».​

​ быстрого доступа. Или​​ функции текстового редактора.​Приведенную Вами формулу,​ lCnt = lCnt​ не совпадает:4 и​
​ примере), а также​wowik7​ с использованием «Данные»​нажмите кнопку​

​Фамилии​​ которое Excel выберет​Или от ExCELL​»Прикрепленные файлы​ первом же столбце?​ одному номеру, а​ поэтому его удобнее​ Притом что сама​
​ о комбинациях клавиш​ Ничего лишнего в​ я уже видела​ + 1 With​ 3. Учту.​ нет ячеек в​

​: Изменил своё первое​​ —> «Работа с​Текст по столбцам​столбцом.​ автоматически.​ отталкиваемся?​primer 002.xlsm (17.68​Юрий М​
​ есть даже и​
​ задавать, редактируя непосредственно​ эта функция не​ CTRL+Z и CTRL+Y.​ нем нет.​ здесь в других​

​ wshResult .Cells(lCnt, 1)​​JayBhagavan​ столбце D с​ сообщение. Прикрепил там​ данными» —> «Текст​.​

​Советы:​​В поле​vikttur​ КБ)»? Что это​: Вариант.​

planetaexcel.ru

Разделение текста по строкам

​ по 20 номеров​​ в самой ячейке.​ разбивает строку на​​Редактировать содержимое ячеек можно​

​ вопросах, но я​​ = arrDataIn(i, 1)​
​: запускать когда целевой​
​ одним словом -​
​ файл​ по столбцам»​

​Следуйте инструкциям в​​ ​

​Поместить в​​: Именно. Без ячеек​ даёт? Да и​mer1k​ через запятую.​Читайте так же: как​ слова, а оптимизирует​

​В режиме редактирования, ячейки​​ двумя способами:​ не знаю, как​ .Cells(lCnt, 2) =​

​ лист активный Option​​ то переносите код​Пытливый​:D​Выделяете две нужных​Столбцах мастера​;)

​Сведения об альтернативном способе​​выберите место на​ никак​

planetaexcel.ru

​ вообще можно было​

Источник

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

Добрый день уважаемый читатель!

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

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

Мастер разбора текстов

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

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

  1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:
  • С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
  • Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.
  1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
  2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:
  • Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
  • Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
  • Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

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

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

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

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

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

  1. Выдергиваем отдельные слова по разделителю;
  2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

Разбить по строкам/столбцам

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx
  • Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Диапазоны —Разбить по строкам/столбцам

Эта команда разбивает данные во всех выделенных ячейках на части, используя в качестве разделителя указанные символ/символы. Главная особенность — в отличие от стандартной команды Excel «Разбить по столбцам», данная команда заносит результат разбиения данных в строки либо столбцы в одну строку/столбец, а не каждую часть в свой столбец. К тому же в стандартной команде Excel нет возможности указать несколько символов — только один.

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

    Специальный — можно добавить в поле Разделитель один из специальных символов: неразрывный пробел или перенос на строки. Стандартно такие символы невозможно ввести с клавиатуры, но они могут часто встречаться в ячейках. Например, эти символы часто применяется в выгрузках из различных программ(вроде 1С): неразрывный пробел для разделения цифр или слов, которые не надо переносить на строки, а перенос на строки — для разделения основания платежей и иной информации.
    Если в поле Разделитель уже указан какой-либо символ, то при выборе дополнительно специального символа будет показано сообщение:
  • ДА(YES) — в поле Разделитель будет добавлен указанный специальный символ. Указанные ранее символы не будут удалены.
  • НЕТ(NO) — поле Разделитель будет очищено и в него будет помещен только выбранный специальный символ.

    • Поместить результат:

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

    • Перенос на строки — каждое новое результирующее значение в ячейке будет записано с новой строки:
    • Произвольный — любой символ, буква, цифра или группа символов(словосочетания, цифры и т.п.).

    Транспонировать — если Диапазон значений представляет собой столбец, то данные после обработки будут помещены в одну строку. Если Диапазон значений — строка, то данные будут помещены в столбец. Недоступно при выводе результата в одну ячейку.

    Финансы в Excel

    Текст по столбцам и строкам

    Подробности Создано 03 Май 2012

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

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

    Напомним, что интерфейсное средство «Текст по столбцам» доступно через меню и ленту Excel Данные Текст по столбцам.

    В файле-примере показано два типа формулы для разделения текста на составляющие:

    • текст по столбцам (диапазон B5:K6)
    • текст по строкам (диапазон A10:B19)

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

    Формула в ячейке B5 и далее по столбцам обрабатывает исходный текст из ячейки A5:

    Формула в ячейке A10 и далее по строкам обрабатывает исходный текст из ячейки A9:

    Формулы ссылаются только на исходный текст и не требуют наличия других предварительно вычисленных составляющих. Варианты отличаются только автоматическим определением порядкового номера текстовой составляющей, в первом случае — это подформула для вычисления номера от количества столбцов: COLUMNS($B:B); во втором — от количества строк: ROWS($10:10). При копировании диапазон столбцов и строк автоматически расширяется, вычисляя таким образом нужное значение. Вместо этой подформулы можно задать константу или переменную, определяющую требуемый номер. Из результатирующих текстовых значений убираются лишние пробелы при помощи функции TRIM().

    Наверняка можно сделать формулу покороче, используя другие методы поиска. Это просто наш вариант — оригинальный, нигде не подсмотренный 😉

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

    Добрый день уважаемый читатель!

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

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

    Мастер разбора текстов

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

    Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

    1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:
    • С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
    • Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.
    1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
    2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:
    • Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
    • Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
    • Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

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

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

    И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

    А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

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

    В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

    Выдергиваем слова с помощью макросов VBA

    Рассмотрим два способа разделить текст в ячейке:

    1. Выдергиваем отдельные слова по разделителю;
    2. Делим текст без пробелов.

    Способ №1.

    Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

    Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

    Разделить текст по столбцам в Excel

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

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

    Перед разбивкой данных требуется выделить нужный диапазон, а затем кликнуть по пиктограмме «Текст по столбцам» в области «Работа с данными» на ленте вкладки «Данные».

    На экране будет отображено окно мастера распределения текста:

    Весь процесс состоит из трех шагов.

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

    Выберите пункт «С разделителями» и нажмите кнопку «Далее».

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

    Обратите внимание на то, что если разделители идут подряд, то каждый из них образует новый столбец, т.е. 2 таких символа создают 1 пустой столбец, 3 – 2 и т.д. Чтобы избавиться от этого часто нужного действия достаточно поставить галочку на поле «Считать последовательные разделители одним».

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

    Имейте в виду то, что символы-разделители и ограничители удаляются из содержимого разделенных данных.

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

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

    Из возможных настроек предоставлено только несколько форматов (типов данных), а поместить распределенный текст можно только на текущем листе.

    У Вас недостаточно прав для комментирования.

    Источник

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

    Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Еще…Меньше

    Для управления строками текста в данных можно использовать текстовые функции LEFT, MID, RIGHT, SEARCH и LEN. Например, можно распределить имя, середину и фамилию из одной ячейки в три отдельных столбца.

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

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

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

    Имя примера

    Описание

    Имя

    От имени

    Фамилия

    Суффикс

    1

    Иван Иванов

    От имени нет

    Евгений

    Климов

    2

    Сергей Куймина

    Один средний начальный

    Эрик

    S.

    Керуаля

    3

    Янина Б. Г. Bueno

    Два средних инициала

    Янина

    B. Г.

    Bueno

    4

    Кана, Венди Йет

    Фамилия с запятой

    Венди

    Бет

    Кан

    5

    Mary Kay D. Andersen

    Имя, в которое входит две части

    Mary Kay

    D.

    Волкова

    6

    ПолуА Бэрето де Матто

    Фамилия из трех части

    Паула

    Ермно-де-Матто

    7

    Г-н Муксон (James van Eaton)

    Фамилия с двумя частьми

    Джеймс

    van Eaton

    8

    Бейон мл., Дэн К.

    Фамилия и суффикс с запятой

    Дэн

    K.

    Бекон

    Младший.

    9

    Борис Заметьев III

    С суффиксом

    Гэри

    Альтман

    Iii

    10

    Г-н Артем Ихигов

    С префиксом

    Райан

    Ihrig

    11

    Джулия Taft-Rider

    Фамилия с дефисами

    Покровская

    Taft-Rider

    Примечание: На рисунках в следующих примерах выделение в полном имени показывает символ, который ищется в формуле ПОИСК.

    В этом примере разделяется два компонента: имя и фамилия. Два имени отделены одним пробелом.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Иван Иванов

    От имени нет

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;1))

    =ПРАВБ(A2;LEN(A2)-SEARCH(» «;A2;1))

    1. Имя

      Имя начинается с первого знака в строке (Ю) и заканчивается пятым знаком (пробелом). Формула возвращает пять знаков в ячейке A2, начиная слева.

      Формула для извлечения имени

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Найдите позицию пробела в A2, начиная слева.

    2. Фамилия

      Фамилия отделена от имени пробелом, начинается с пятого знака справа и заканчивается первым знаком справа (а). Формула извлекает в A2 пять знаков справа.

      Формула для извлечения фамилии

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК и ДЛСТР.

      Найдите позицию пробела в A2, начиная слева. (5)

    3. Подсчитайте общую длину текстовой строки, а затем вычтите количество знаков слева до первого пробела, найденное в пункте 1.

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

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Сергей Куймина

    Один средний начальный

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (средний начальный)

    ‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1))

    =MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-ПОИСК(» «;A2;1))

    Формула

    Live Result (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    1. Имя

      Имя начинается с первого знака слева (E) и заканчивается пятым (первым пробелом). Формула извлекает первые пять знаков в A2, начиная слева.

      Формула для разделения имени и фамилии, а также начального имени

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Найдите позицию пробела в A2, начиная слева. (5)

    2. От имени

      От второе имя начинается с шестой позиции (S) и заканчивается в 1-й позиции (второй пробел). Эта формула включает вложенные функции ПОИСК для поиска второго экземпляра пробела.

      Формула извлекает три знака, начиная с шестой позиции.

      Подробности формулы для разделения имени, среднего имени и фамилии

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (5).

    3. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Эта позиция является начальной позицией от имени. (5 + 1 = 6)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)

    4. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    5. Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 4. Этот номер знака является конечной позицией от имени. (8)

    6. Поищите позицию пробела в A2, начиная с первого знака слева. (5)

    7. Возьмите номер знака второго пробела, найденного на шаге 5, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (8 –5 = 3)

    8. Фамилия

      Фамилия начинается с шести знаков справа (K) и заканчивается первым знаком справа (n). Эта формула включает вложенные функции ПОИСК для поиска второго и третьего экземпляров пробела (которые находятся на пятой и пятой позициях слева).

      Формула извлекает шесть знаков в A2, начиная с правого.

      Вторая функция ПОИСК в формуле для разделения имени, середины и фамилии

    9. Используйте функции LEN и вложенные функции ПОИСК, чтобы найти значение для num_chars:

      Поищите позицию пробела в A2, начиная с первого знака слева. (5)

    10. Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    11. Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 2. Этот номер знака является конечной позицией от имени. (8)

    12. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (14 – 8 = 6).

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

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Янина Б. Г. Bueno

    Два средних инициала

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (средние инициалы)

    ‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-ПОИСК(» «;A2;1))

    =MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-ПОИСК(» «;A2;1))-ПОИСК(» «;A2;1))

    Формула

    Live Result (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    1. Имя

      Первое имя начинается с первого знака слева (J) и заканчивается символом слева (первый пробел). Формула извлекает первые восемь знаков в A2, начиная слева.

      Формула для разделения имени, фамилии и двух инициалов

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Наищите позицию первого пробела в A2, начиная слева. (8)

    2. От имени

      От второе имя начинается с позиций ва (B) и заканчивается 17-й позицией (третий пробел). Эта формула включает вложенную формулу ПОИСК для поиска первого, второго и третьего пробелов в первой, 11-й и 17-й позициях.

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

      Формула для разделения имени, фамилии и двух инициалов

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)

    3. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Эта позиция является начальной позицией от имени. (8 + 1 = 9)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)

    4. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (8 + 1 = 9)

    5. Найщите вторую позицию в A2, начиная с позиции в направлении «в» (B), найденной в шаге 4. (11).

    6. Чтобы получить позицию знака после второго пробела (G), добавьте 1. Этот номер знака является начальной позицией, с которой нужно начать поиск третьего пробела. (11 + 1 = 12)

    7. Поиск третьего пробела в A2, начиная с двенадцатой позиции, найденной в шаге 6. (14)

    8. Поищите позицию первого пробела в A2. (8)

    9. Возьмите номер третьего пробела, найденного на шаге 7, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с позиций, найденной на шаге 2.

    10. Фамилия

      Фамилия начинается с пяти знаков справа (B) и заканчивается первым знаком справа (o). Эта формула включает вложенную формулу ПОИСК для поиска первого, второго и третьего пробелов.

      Формула извлекает пять знаков в A2, начиная справа от полного имени.

      Формула для разделения имени, фамилии и двух инициалов

      Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (8)

    11. Чтобы получить позицию знака после первого пробела (B), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (8 + 1 = 9)

    12. Найщите вторую позицию в A2, начиная с позиции в направлении «в» (B), найденной на шаге 2. (11)

    13. Чтобы получить позицию знака после второго пробела (G), добавьте 1. Этот номер знака является начальной позицией, с которой нужно начать поиск третьего пробела. (11 + 1 = 12)

    14. Поиск третьего пробела в A2, начиная с двенадцатой позиции (G), найденной в шаге 6. (14)

    15. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (19 – 14 = 5)

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

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Кана, Венди Йет

    Фамилия с запятой

    Формула

    Результат (имя)

    ‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1))

    =MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-ПОИСК(» «;A2;1))

    Формула

    Результат (от имени)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    Формула

    Live Result (фамилия)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1)-2)

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1)-2)

    1. Имя

      Имя начинается с седьмого знака слева (В) и заканчивается двенадцатым (второй пробел). Поскольку имя оказывается в середине полного имени, для его извлечения необходимо воспользоваться функцией ПСТР.

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

      Формула для разделения фамилии, имени и отчества

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)

    2. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Эта позиция является начальной позицией имени. (6 + 1 = 7)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)

    3. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)

      Поиск второго пробела в A2, начиная с седьмой позиции (W), найденной в шаге 4. (12)

    4. Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)

    5. Возьмите номер знака второго пробела, найденного на шаге 5, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых из текстовой строки, начиная с седьмой позиции, найденной на шаге 2. (12 – 6 = 6)

    6. От имени

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

      Формула извлекает четыре знака, начиная справа.

      Формула для разделения фамилии, имени и отчества

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК и ДЛСТР:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)

    7. Чтобы получить позицию знака после первого пробела (О), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)

    8. Поиск второго пробела в A2, начиная с седьмой позиции (W), найденной на шаге 2. (12)

    9. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (16 – 12 = 4)

    10. Фамилия

      Фамилия начинается с первого знака слева (К) и заканчивается четвертым знаком (н). Эта формула извлекает четыре знака, начиная слева.

      Формула для разделения фамилии, имени и отчества

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (6)

    11. Вычитать 2, чтобы получить позицию последнего знака фамилии (n). Результатом будет количество символов, извлекаемых с левой стороной. (6 – 2 =4)

    В этом примере используется имя из двух части — Mary Kay. Каждый компонент имен разделяется вторым и третьим пробелами.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Mary Kay D. Andersen

    Имя, в которое входит две части

    Формула

    Результат (имя)

    ЛЕВ ЛЕВЫЙ(A2; ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

    Формула

    Результат (средний начальный)

    ‘=MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

    =MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1))

    Формула

    Live Result (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    1. Имя

      Первое имя начинается с первого знака слева и заканчивается знаком слева (второй пробел). Эта формула включает вложенный поиск, чтобы найти второй пробел слева.

      Формула извлекает девять знаков, начиная слева.

      Формула для разделения имени, среднего имени, инициала и фамилии

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)

    2. Чтобы получить позицию знака после первого пробела (К), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    3. Поиск второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. Результат — количество символов, извлекаемых левеем из текстовой строки. (9)

    4. От имени

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

      Формула извлекает два знака из середины, начиная с десятой позиции.

      Формула для разделения имени, среднего имени, инициала и фамилии

      Используйте вложенные функции ПОИСК, чтобы найти значение для start_num:

      Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)

    5. Добавьте 1, чтобы получить символ после первого пробела (K). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    6. Поищите позицию второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. Результат — количество символов, извлекаемых левее. (9)

    7. Добавьте 1, чтобы получить символ после второго пробела (D). Результат — начальная позиция от имени. (9 + 1 = 10)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (10)

    8. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (12)

    9. Поищите позицию знака после второго пробела (D). Результат — начальная позиция от имени. (10)

    10. Возьмите номер третьего пробела, найденного на шаге 6, и вычитайте номер знака «D», найденный на шаге 7. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с десятой позиции, найденной в шаге 4. (12 – 10 = 2)

    11. Фамилия

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

      Формула извлекает восемь знаков справа.

      Формула для разделения имени, среднего имени, инициала и фамилии

      Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars:

      Наищите позицию первого пробела в A2, начиная слева. (5)

    12. Добавьте 1, чтобы получить символ после первого пробела (K). Результат — это номер символа, с которого нужно начать поиск пробела. (5 + 1 = 6)

    13. Поиск второго пробела в A2, начиная с шестой позиции (K), найденной на шаге 2. (9)

    14. Чтобы получить позицию знака после второго пробела (D), добавьте 1. Результат — начальная позиция от имени. (9 + 1 = 10)

    15. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (12)

    16. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (20 – 12 = 8)

    В этом примере используется фамилия из трех части: Ермолето де Матто. Первый пробел пометит конец имени и начало фамилии.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    ПолуА Бэрето де Матто

    Фамилия из трех части

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (фамилия)

    ПРАВБ(A2;LEN(A2)-ПОИСК(» «;A2;1))

    =ПРАВБ(A2;LEN(A2)-SEARCH(» «;A2;1))

    1. Имя

      Имя начинается с первого знака слева (P) и заканчивается шестым (первый пробел). Формула извлекает шесть знаков слева.

      Формула для разделения имени и фамилии из трех разделов

      Чтобы найти значение для num_chars, используйте функцию Num_chars.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    2. Фамилия

      Фамилия начинает символы справа (B) и заканчивается первым знаком справа (s). Формула извлекает символы справа.

      Формула для разделения имени и фамилии из трех разделов

      Чтобы найти значение для num_chars, используйте функции LEN и NUM_CHARS.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    3. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до первого пробела, найденного на шаге 1. Результат — количество символов, извлекаемого справа от полного имени. (23 – 6 = 17)

    В этом примере используется фамилия из двух видов: van Eaton. Первый пробел пометит конец имени и начало фамилии.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Г-н Муксон (James van Eaton)

    Фамилия с двумя частьми

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;1))

    =ПРАВБ(A2;LEN(A2)-SEARCH(» «;A2;1))

    1. Имя

      Первое имя начинается с первого знака слева (J) и заканчивается символом слева (первый пробел). Формула извлекает шесть знаков слева.

      Формула для разделения имени и фамилии из двух частей

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    2. Фамилия

      Фамилия начинается с символа справа (v) и заканчивается первым знаком справа (n). Формула извлекает девять знаков справа от полного имени.

      Формула для разделения имени и фамилии из двух частей

      Чтобы найти значение для num_chars, используйте функции LEN и NUM_CHARS.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    3. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до первого пробела, найденного на шаге 1. Результат — количество символов, извлекаемого справа от полного имени. (15 – 6 = 9)

    В этом примере фамилия будет первой, а за ней — суффикс. Запятая отделяет фамилию и суффикс от имени и от среднего инициалов.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Бейон мл., Дэн К.

    Фамилия и суффикс с запятой

    Формула

    Результат (имя)

    ‘=MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    =MID(A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1)-SEARCH(» «;A2;1)))

    Формула

    Результат (средний начальный)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)+1))

    Формула

    Результат (фамилия)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (суффикс)

    ‘=MID(A2;ПОИСК(» «; A2;1)+1;(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-2)-SEARCH(» «;A2;1))

    =MID(A2;ПОИСК(» «; A2;1)+1;(ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-2)-SEARCH(» «;A2;1))

    1. Имя

      Имя начинается с двенадцатого знака (D) и заканчивается 15-м (третий пробел). Формула извлекает три знака, начиная с двенадцатой позиции.

      Формула для первого разделения фамилии и суффикса с запятой

      Используйте вложенные функции ПОИСК, чтобы найти значение для start_num:

      Наищите позицию первого пробела в A2, начиная слева. (6)

    2. Добавьте 1, чтобы получить символ после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)

    3. Найщите вторую позицию в A2, начиная с седьмой позиции (J), найденной на шаге 2. (11)

    4. Добавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Поищите позицию знака после второго пробела (D). Результатом будет номер знака, с которого вы хотите начать поиск третьего пробела. (12)

    5. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция имени. (15)

    6. Поищите позицию знака после второго пробела (D). Результат — начальная позиция имени. (12)

    7. Возьмите номер третьего пробела, найденного на шаге 6, и вычитайте номер знака «D», найденный на шаге 7. Результатом является количество символов, извлекаемого с позиции, которая начинается с двенадцатой строки, найденной в шаге 4. (15 – 12 = 3)

    8. От имени

      От второе имя начинается со второго знака справа (K). Формула извлекает два знака справа.

      Формула для первого разделения фамилии и суффикса с запятой

      Наищите позицию первого пробела в A2, начиная слева. (6)

    9. Добавьте 1, чтобы получить символ после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (6 + 1 = 7)

    10. Найщите вторую позицию в A2, начиная с седьмой позиции (J), найденной на шаге 2. (11)

    11. Добавьте 1, чтобы получить символ после второго пробела (D). Результатом является начальная позиция имени. (11 + 1 = 12)

    12. Наищите позицию третьего пробела в A2, начиная слева. Результат — конечная позиция от имени. (15)

    13. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до третьего пробела, найденного на шаге 5. Результат — количество символов, извлекаемого справа от полного имени. (17 – 15 = 2)

    14. Фамилия

      Фамилия начинается с первого знака слева (B) и заканчивается шестым (первый пробел). Поэтому формула извлекает шесть знаков слева.

      Формула для первого разделения фамилии и суффикса с запятой

      Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    15. Суффикс

      Суффикс начинается с седьмого знака слева (J) и заканчивается символом слева (.). Формула извлекает три знака, начиная с седьмого.

      Формула для первого разделения фамилии и суффикса с запятой

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

      Наищите позицию первого пробела в A2, начиная слева. (6)

    16. Добавьте 1, чтобы получить символ после первого пробела (J). Результат — начальная позиция суффикса. (6 + 1 = 7)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Наищите позицию первого пробела в A2, начиная слева. (6)

    17. Добавьте 1, чтобы получить позицию символа после первого пробела (J). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (7)

    18. Поищите позицию второго пробела в A2, начиная с седьмого знака, найденного в шаге 4. (11)

    19. Вычитать 1 из номера знака второго пробела, найденного в шаге 4, чтобы получить номер знака «,». Результат — конечная позиция суффикса. (11 — 1 = 10)

    20. Поищите позицию первого пробела в числе. (6)

    21. Найдя первый пробел, добавьте 1, чтобы найти следующий символ (J), который также находится в шагах 3 и 4. (7)

    22. Возьмите номер знака «», найденный на шаге 6, и вычитайте номер символа «J», найденный в шагах 3 и 4. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с седьмой позиции, найденной в шаге 2. (10 — 7 = 3)

    В этом примере первое имя находится в начале строки, а суффикс — в конце, поэтому можно использовать формулы, похожие на пример 2. Для извлечения имени используйте функцию ЛЕВША, для извлечения фамилии — функцию MID, а для извлечения суффикса — функцию ПРАВБ.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Борис Заметьев III

    Имя и фамилия с суффиксом

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (фамилия)

    ‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-(ПОИСК(» «;A2;1)+1))

    =MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-(ПОИСК(» «;A2;1)+1))

    Формула

    Результат (суффикс)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    1. Имя

      Имя начинается с первого знака слева (G) и заканчивается пятым (первый пробел). Поэтому формула извлекает пять знаков слева от полного имени.

      Формула для разделения имени и фамилии с суффиксом

      Наищите позицию первого пробела в A2, начиная слева. (5)

    2. Фамилия

      Фамилия начинается с шестого знака слева (A) и заканчивается одиннадцатым (второй пробел). Эта формула включает вложенный поиск для поиска позиций пробелов.

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

      Формула для разделения имени и фамилии с суффиксом

      Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:

      Наищите позицию первого пробела в A2, начиная слева. (5)

    3. Чтобы получить позицию знака после первого пробела (A), добавьте 1. Результат — начальная позиция фамилии. (5 + 1 = 6)

      Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.

      Наищите позицию первого пробела в A2, начиная слева. (5)

    4. Чтобы получить позицию знака после первого пробела (A), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    5. Поищите позицию второго пробела в A2, начиная с шестого знака, найденного в шаге 4. Этот номер знака является конечной позицией фамилии. (12)

    6. Поищите позицию первого пробела в числе. (5)

    7. Добавьте 1, чтобы найти позицию знака после первого пробела (A), также найденного в шагах 3 и 4. (6)

    8. Возьмите номер второго пробела, найденного на шаге 5, и вычитайте номер знака «A», найденный в шагах 6 и 7. Результатом является количество символов, извлекаемых из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (12 – 6 = 6)

    9. Суффикс

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

      Формула для разделения имени и фамилии с суффиксом

      Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars:

      Наищите позицию первого пробела в A2, начиная слева. (5)

    10. Добавьте 1, чтобы получить символ после первого пробела (A). Результатом будет номер знака, с которого нужно начать поиск второго пробела. (5 + 1 = 6)

    11. Поиск второго пробела в A2, начиная с шестой позиции (A), найденной на шаге 2. (12)

    12. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (15 – 12 = 3)

    В этом примере перед полным именем предшествует префикс, и вы используете формулы, аналогичные примеру 2: функция MID для извлечения имени, функция ПРАВБ для извлечения фамилии.

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Г-н Артем Ихигов

    С префиксом

    Формула

    Результат (имя)

    ‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-(ПОИСК(» «;A2;1)+1))

    =MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-(ПОИСК(» «;A2;1)+1))

    Формула

    Результат (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    =ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))

    1. Имя

      Первое имя начинается с пятого знака слева (R) и заканчивается символом второго знака (второй пробел). Формула вложена в поиск, чтобы найти позиции пробелов. Он извлекает четыре знака, начиная с пятой позиции.

      Формула для разделения имени, предшествующего префиксу

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

      Наищите позицию первого пробела в A2, начиная слева. (4)

    2. Чтобы получить позицию знака после первого пробела (R), добавьте 1. Результатом является начальная позиция имени. (4 + 1 = 5)

      Используйте вложенную функцию ПОИСК, чтобы найти значение для num_chars:

      Наищите позицию первого пробела в A2, начиная слева. (4)

    3. Чтобы получить позицию знака после первого пробела (R), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (4 + 1 = 5)

    4. Поищите позицию второго пробела в A2, начиная с пятого знака, найденного в шагах 3 и 4. Этот номер знака является конечной позицией имени. (9)

    5. Наймем первый пробел. (4)

    6. Добавьте 1, чтобы найти позицию знака после первого пробела (R), также найденного в шагах 3 и 4. (5)

    7. Возьмите номер второго пробела, найденного на шаге 5, и вычитайте номер знака «R», найденный в шагах 6 и 7. Результатом является количество символов, извлекаемого из текстовой строки, начиная с пятой позиции, найденной на шаге 2. (9 – 5 = 4)

    8. Фамилия

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

      Формула для разделения имени, предшествующего префиксу

      Используйте вложенные функции ПОИСК и LEN, чтобы найти значение для num_chars:

      Наищите позицию первого пробела в A2, начиная слева. (4)

    9. Чтобы получить позицию знака после первого пробела (R), добавьте 1. Результатом будет номер знака, с которого нужно начать поиск второго пробела. (4 + 1 = 5)

    10. Поиск второго пробела в A2, начиная с пятой позиции (R), найденной на шаге 2. (9)

    11. Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (14 – 9 = 5)

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

    Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.

    Совет.    Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.

    Имя примера

    Описание

    Джулия Taft-Rider

    Фамилия с дефисами

    Формула

    Результат (имя)

    ‘=ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    =ЛЕВЫЙ(A2; ПОИСК(» «;A2;1))

    Формула

    Результат (фамилия)

    ‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;1))

    =ПРАВБ(A2;LEN(A2)-SEARCH(» «;A2;1))

    1. Имя

      Имя начинается с первого знака слева и заканчивается на шестой позиции (первый пробел). Формула извлекает шесть знаков слева.

      Формула для разделения имени и фамилии с дефисами

      Функция ПОИСК используется для поиска значения num_chars:

      Наищите позицию первого пробела в A2, начиная слева. (6)

    2. Фамилия

      Вся фамилия начинается с десяти знаков справа (T) и заканчивается первым знаком справа (r).

      Формула для разделения имени и фамилии с дефисами

      Чтобы найти значение для num_chars, используйте функции LEN и NUM_CHARS.

      Поищите позицию пробела в A2, начиная с первого знака слева. (6)

    3. Подсчитайте общую длину извлекаемой текстовой строки и вычитайте количество знаков слева до первого пробела, найденного на шаге 1. (16 – 6 = 10)

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

    Источник

    Понравилась статья? Поделить с друзьями:

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

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

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии