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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ЛЕВСИМВ(

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

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

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

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

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

ПОИСК()

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

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

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

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

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

-1

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

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

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

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

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

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

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

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

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

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

=ПСТР(

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

  1. Тут все делаем по аналогии, как и в первом примере.

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

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

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

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

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

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

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

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

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

  1. Нам нужно во втором поиске в последней строчке дописать:

+1

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

  1. Переходим в ПСТР.
  2. Выделяем строчку «Количество_знаков».
  3. Ставим курсор в самый конец формулы.
  4. Копируем и вставляем вот эту функцию:

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

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

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

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

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

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

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

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

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

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

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

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

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

Автор статьи
Бородач 2135 статей
Сенсей по решению проблем с WiFiем. Обладатель оленьего свитера, колчана витой пары и харизматичной бороды. Любитель душевных посиделок за танками.
WiFiGid
Комментарии: 3
  1. Сергей

    Спасибо тебе дружище, а то я уже думал вручную все это делать буду.

  2. Аноним

    Полезная конечно штука. :grin: :idea:

  3. Борис Сергеевич

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

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

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