Всё об операционных системах — подробное руководство для пользователей операционных систем

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

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

Инструмент для деления по столбцам в Excel

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

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

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

    Жмем текст по столбцам
    Жмем текст по столбцам
  2. После нажатия на указанную кнопку, на экране появится новое окно — Мастер разделения текста по столбцам. Необходимо выбрать формат данных. В нашем случае это параметр «С разделителями». После этого нажимаем Далее.

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

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

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

    Результат выполнения
    Результат выполнения

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

Как разбить текст на столбцы с помощью формулы

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

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

Этап №1: извлекаем первое слово

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

  1. Выбираем клетку для записи результата и после знака «=» вводим формулу ЛЕВСИМВ. В качестве первого аргумента в скобках записываем ссылку на ячейку, из которой будет браться значение. После этого ставим точку с запятой.

    Вводим формулу ЛЕВСИМВ
    Вводим формулу ЛЕВСИМВ
  2. Далее нужно указать количество символов, которые будут добавлены в ячейку. Однако точное число знаков задать нельзя, так как каждая фамилия имеет разную длину. Поэтому вставляем в качестве аргумента функцию «ПОИСК», где в качестве аргумента указываем пробел и адрес на ячейку, в которой ведется поиск. За скобками также добавляем «–1», чтобы в ячейку добавлялись знаки до первого пробела.

    Добавляем функцию поиск
    Добавляем функцию поиск

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

Результат выполнения
Результат выполнения

Этап №2: извлекаем второе слово

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

  1. Выделяем ячейку для записи результата и вставляем формулу ПСТР. Она ищет фрагмент в заданной ячейке, поэтому в качестве первого аргумента указываем адрес клетки с ФИО.

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

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

    Добавляем условия для определения числа знаков
    Добавляем условия для определения числа знаков

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

Результат выполнения
Результат выполнения

Этап 3: извлекаем третье слово

Завершающий и относительно простой этап. В данном случае при делении текста на столбцы в Excel будем использовать вспомогательную формулу ДЛСТР:

  1. Выделяем ячейку для записи результата. Вводим основную формулу — ПРАВСИМВ. Она возвращает определенное число знаков из правой части ячейки. В качестве первого аргумента указываем ссылку на клетку с текстом.

    Вводим формулу ПРАВСИМВ
    Вводим формулу ПРАВСИМВ
  2. Далее нужно определить, сколько знаков нужно получить. Так как длина отчества отличается в каждой строке, нужно написать универсальную формулу применимую для разных значений. Здесь используем функцию ДЛСТР. Вводим данную формулу и в качестве первого аргумента указываем адрес исходной ячейки с данными. Она найдет общее число символов в заданной ячейке.

    Вводим формулу ДЛСТР
    Вводим формулу ДЛСТР
  3. Чтобы определить количество символов, из которых состоит третье слово, нужно отнять длину первого и второго слова от общего числа знаков. В конце также добавляем «+1» — это пробел между именем и фамилией.

    Добавляем ДЛСТР для определения длины слова
    Добавляем ДЛСТР для определения длины слова

В результате получим отчество из ячейки с ФИО. Таким образом мы выяснили, как разделить текст из 1 ячейки на 3 столбца. Способ менее удобный, чем инструмент для автоматического деления текста, однако действенный и может рассматриваться в качестве альтернативы.

Результат выполнения
Результат выполнения

Заключение

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

Видео по теме

Adblock
detector