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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ЛЕВСИМВ(

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

4. Выделяем строку «Текст» и с помощью ЛКМ указываем первую ячейку.

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

ПОИСК()

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

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

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

-1

10. Зачем это нужно? – это нужно для того, чтобы наша сборная функция выделяла только слово без конечного пробела. Обратите внимание на конечные результаты после строчек, которые расположены после знака (=) – можно сказать это предварительные результаты. Как вы можете понять, функция выделяет строчку «Петров Петр Иванович». Далее мы находим расположение первого пробела – это 7 символ. Делаем вычитание, чтобы выделить только фамилию без пробела. В итоге функция возвращает только слово «Петров».

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. Урок получился достаточно информативный. Советую его сохранить в закладках вашего браузера, чтобы использовать статью в качестве шпаргалки.