Методы разделения текста в ячейке Excel по разным столбцам
Содержание
При оформлении таблицы перед пользователем может встать необычная на первый взгляд задача — разделить существующий текст в ячейке Excel по столбцам. В клетках неудобно хранить большие объемы текста, поэтому чаще всего их делят и помещают в отдельные ячейки. В данном материале рассмотрим, как разделить столбец на несколько, заполнив значениями каждый из них.
Инструмент для деления по столбцам в Excel
В MS Excel предусмотрен специальный инструмент, позволяющий расцепить текст из одной ячейки, разделив его на несколько. Это очень удобная функция, которая поможет разделить клетки с текстом на два, три или более столбцов.
Чаще всего этот инструмент применяется в том случае, если нужно разбить по колонкам записанные в ячейки ФИО. Также разделение текста может использоваться тогда, когда в колонках записаны определенные параметры объекта, например, характеристики товара. Рассмотрим распространенную ситуацию — деление ячеек с ФИО на три колонки.
- Выделяем один столбец — тот, который необходимо разделить. Далее открываем вкладку «Данные» и выбираем инструмент «Текст по столбцам».
- После нажатия на указанную кнопку, на экране появится новое окно — Мастер разделения текста по столбцам. Необходимо выбрать формат данных. В нашем случае это параметр «С разделителями». После этого нажимаем .
- В нашем примере в столбце записаны фамилия, имя и отчество, между которыми находится один пробел. Поэтому в качестве разделителя необходимо выбрать именно пробел. При этом внизу будет отображен образец разбора данных. Задав разделитель, нажимаем .
- В следующем окне вы можете задать формат, в котором будут записаны данные из разделенной ячейки. В нашем случае это простой текст, поэтому можно оставить значение «Общий». Ниже в строке «Поместить в» можно указать адрес ячейки, с которой будет начинаться разделенный фрагмент.
- Указав формат и место для размещения столбцов, нажимаем . В результате данные из первого столбца будут разделены на 3 и в таблице появятся новые колонки с фамилией, именем и отчеством соответственно.
Как видно, инструмент простой в применении и очень удобный. С его помощью можно разделить ячейку на несколько независимо от того, сколько слов в ней содержится. Поэтому данный метод подойдет не только для ФИО, но и для любых других данных. Причем это необязательно должны быть именно слова. Аналогичный подход действует с числами и датами, при условии наличия между ними разделителей.
Как разбить текст на столбцы с помощью формулы
Это более сложный метод, который лучше всего подойдет именно опытным пользователям. Он будет удобен в том случае, если ячейки содержат не более двух или трех слов. С помощью формулы можно разделить столбец на 2 или на 3.
Однако здесь следует учитывать нюанс. Фактически изначальная ячейка не делится на фрагменты. Применяемая формула просто извлекает нужное значение и вставляет его в заданную клетку. Тем не менее этот метод может использоваться, если нужно разделить ячейки на две части или максимум на три. С большим числом делений использовать данный способ уже неудобно и намного проще воспользоваться предыдущим методом. Рассмотрим далее, как в Excel разделить текст по столбцам за счет построения и применения формулы на примере пошагово.
Этап №1: извлекаем первое слово
Как уже было отмечено, суть метода заключается не в том, чтобы разделить текст по столбцам, а в том, чтобы заполнить ячейки нужными значениями. Для получения первого слова, в нашем случае фамилии, будет применяться формула ЛЕВСИМВ:
- Выбираем клетку для записи результата и после знака «=» вводим формулу ЛЕВСИМВ. В качестве первого аргумента в скобках записываем ссылку на ячейку, из которой будет браться значение. После этого ставим точку с запятой.
- Далее нужно указать количество символов, которые будут добавлены в ячейку. Однако точное число знаков задать нельзя, так как каждая фамилия имеет разную длину. Поэтому вставляем в качестве аргумента функцию «ПОИСК», где в качестве аргумента указываем пробел и адрес на ячейку, в которой ведется поиск. За скобками также добавляем «–1», чтобы в ячейку добавлялись знаки до первого пробела.
В результате этого мы получим фамилию из изначальной ячейки. Можно воспользоваться маркером автозаполнения и применить формулу к остальным строкам в столбце.
Этап №2: извлекаем второе слово
Рассматривая вопрос, как в Экселе разделить текст ячейки на 3 колонки, сразу же отметим, что второй этап будет самым трудоемким. Это связано с тем, что необходимо будет выполнить поиск позиции второго слова между двумя пробелами. Поэтому формула будет более массивная и сложная для понимания:
- Выделяем ячейку для записи результата и вставляем формулу ПСТР. Она ищет фрагмент в заданной ячейке, поэтому в качестве первого аргумента указываем адрес клетки с ФИО.
- Следующий шаг — определение начальной позиции. Оно зависит от длины первого слова, поэтому указать строгое число символов нельзя. Здесь также будет использоваться формула ПОИСК. В качестве аргументов указываем пробел и адрес исходной ячейки. За скобками добавляем «+1», чтобы искомое слово начиналось с первой буквы, а не с пробела.
- Последний аргумент — количество знаков, которые должна вернуть формула. Как и в случае с именем, число символов варьируется. Поэтому вводим формулу поиска повторно. Причем внутри также будет содержаться формула «ПОИСК», которая будет искать второй пробел в ячейке. Для завершения расчета необходимого числа символов в конце конструкции добавляем «-ПОИСК(" ";A2)–1». В результате получаем массивную формулу, которая возвращает имя.
Применяем записанную формулу ко всем ячейкам. Так мы станем еще на шаг ближе к тому, чтобы в Excel разделить строку на столбцы.
Этап 3: извлекаем третье слово
Завершающий и относительно простой этап. В данном случае при делении текста на столбцы в Excel будем использовать вспомогательную формулу ДЛСТР:
- Выделяем ячейку для записи результата. Вводим основную формулу — ПРАВСИМВ. Она возвращает определенное число знаков из правой части ячейки. В качестве первого аргумента указываем ссылку на клетку с текстом.
- Далее нужно определить, сколько знаков нужно получить. Так как длина отчества отличается в каждой строке, нужно написать универсальную формулу применимую для разных значений. Здесь используем функцию ДЛСТР. Вводим данную формулу и в качестве первого аргумента указываем адрес исходной ячейки с данными. Она найдет общее число символов в заданной ячейке.
- Чтобы определить количество символов, из которых состоит третье слово, нужно отнять длину первого и второго слова от общего числа знаков. В конце также добавляем «+1» — это пробел между именем и фамилией.
В результате получим отчество из ячейки с ФИО. Таким образом мы выяснили, как разделить текст из 1 ячейки на 3 столбца. Способ менее удобный, чем инструмент для автоматического деления текста, однако действенный и может рассматриваться в качестве альтернативы.
Заключение
Разделение текста, содержащегося в одном столбце на несколько колонок — довольно сложная задача, особенно для тех, кто мало знаком с функционалом MS Excel. Проще всего выполнить данную операцию с помощью инструмента деления текста, который позволяет получить несколько новых столбцов из одного исходного. В качестве альтернативного метода может рассматриваться возможность применения формул, однако этот способ довольно трудоемкий и достаточно сложный.