Текст по столбцам в Excel: как разделить и разбить?

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

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

В статье ниже мы рассмотрим два способа. Первый подойдет для быстрого разового разделения данных, а второй вариант поможет разобраться с формулами и подойдет, если вы хотите автоматически обрабатывать новые строки. Если при прочтении что-то будет не получаться – пишите в комментариях, и портал WiFiGiD.RU вам поможет. Поехали!

Способ 1: Специальная функция

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

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

1. Выделите те ячейки, которые хотите разбить. Используем для этого зажатую левую кнопку мыши (ЛКМ).
2. После этого на верхней панели переходим в раздел «Данные».

3. В этом разделе находим подраздел «Работа с данными» – там нажимаем по кнопке «Текст по столбцам».

4. В первом шаге проверьте, чтобы стояла настройка «с разделителями» – это означает, что слова или значения в ячейке разделены каким-то знаком. Например, пробелом, запятой или точкой с запятой. В нашем случае это будет пробел. Нажимаем «Далее».

5. Оставляем галочку напротив «пробела», хотя по умолчанию может быть выбран «знак табуляции». Если он вам не нужен, лучше его убрать.

Читаем также – что такое табуляция и для чего этот знак нужен.

6. На последнем шаге оставляем все как есть. Но перед тем как нажать «Готово», давайте разберемся, что тут происходит. Excel предлагает выбрать формат будущих ячеек. По умолчанию используется вариант «Общий» – программа сама пытается определить, где число, дата, текст и другие значения.

Обязательно ознакомьтесь с уроком про формат ячеек в Excel.

ВНИМАНИЕ! Если вы разделяете данные с номерами телефонов, кодами товаров или другими значениями, где важны нули в начале, лучше выбрать формат «Текст». Иначе Excel может автоматически удалить первые нули.

7. Как видите, теперь данные с ФИО разбиты по разным столбцам. Для удобства можно добавить сверху строку с названиями столбцов: «Фамилия», «Имя» и «Отчество».

Читаем также – как добавить строку в таблицу Excel.

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

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

Способ 2: Формула разделителя

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

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

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

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

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

=ЛЕВСИМВ(

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

5. Во вторую строчку «Количество_знаков» вписываем функцию:

ПОИСК()

6. Чтобы перейти к работе второй функции, нужно один раз нажать ЛКМ по этой функции в строке с данными.

7. Теперь работаем со второй функцией. В поле «Искомый_текст» вписываем разделитель. В нашем случае это пробел.
8. В поле «Текст_для_поиска» указываем первую ячейку.

9. Возвращаемся в работу с первой формулой, нажав по «ЛЕВСИМВ». Во вторую строчку дописываем:

-1

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

11. Применяем функцию, нажав «ОК». Теперь используем маркер автозаполнения, чтобы заполнить формулу во всех ячейках.

Читаем – что такое маркер автозаполнения в Excel.

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

=ЛЕВСИМВ(A1;ПОИСК(” “;A1)-1)

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

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

=ПСТР(

2. Далее все делаем по аналогии с первым примером.

3. Указываем пробел и ячейку и обратно переходим в настройки ПСТР.

4. Дописываем (+1), так как нам нужно получить позицию первой буквы имени. В строку «Количество_знаков» вписываем новый поиск.

5. Вводим те же значения, но в последней строчке «Нач_позиция» добавляем еще один ПОИСК.

6. Переходим уже в третий ПОИСК и указываем те же значения.
7. Переключаемся на второй ПОИСК.

8. Во втором поиске в последней строчке дописываем:

+1

9. Переходим в ПСТР.
10. Выделяем строчку «Количество_знаков».
11. Ставим курсор в конец формулы.
12. Копируем и вставляем функцию:

-ПОИСК(” “;A1)-1)

13. Применяем формулу и растягиваем ее на другие ячейки.

Сама формула:

=ПСТР(A1;ПОИСК(” “;A1)+1;ПОИСК(” “;A1;ПОИСК(” “;A1)+1)-ПОИСК(” “;A1)-1)

Если говорить проще, то функция работает так:

1. Мы находим расположение первого символа после первого пробела.
2. Далее находим следующий разделитель.
3. После этого выделяем только слово между двумя пробелами.

Теперь нам нужно найти последнее слово. Для этого строим формулу:

=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(” “;A1;ПОИСК(” “;A1)+1))

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

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

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