ГлавнаяРазноеКак разделить текст в excel по столбцам

Разделение строки на столбцы в Excel. Как разделить текст в excel по столбцам


Как разбить ячейки в excel: «текст по столбцам», «мгновенное заполнение» и формулы

В данной статье Вы отыщете пара способов, как разбить ячейки либо целые столбцы в Excel 2010 и 2013. Приведённые скриншоты и примеры иллюстрируют работу с инструментами «Текст по столбцам» и «Мгновенное заполнение», также Вы заметите подборку формул для разделения имён, текстовых и числовых значений. Данный урок окажет помощь Вам выбрать наилучший способ разбиения данных в Excel.

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

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

Инструмент «Текст по столбцам » вправду весьма эргономичен, в то время, когда необходимо поделить эти из одного столбца по нескольким в Excel 2013, 2010, 2007 либо 2003.

«Текст по столбцам» разрешает разбивать значения ячеек, отделённые разделителями, либо выделять эти фиксированной ширины (в то время, когда все значения содержат определённое количество знаков). Давайте разглядим эти варианты подробнее:

Разбиваем текстовые эти с разделителями по столбцам в Excel

Предположим, имеется перечень участников, приглашённых на конференцию либо какое-то второе мероприятие. На рисунке ниже видно, что в столбце Participant (Участник) перечислены имена участников, государство и ожидаемая дата прибытия:

Нужно разбить данный текст на отдельные столбцы, дабы таблица имела следующие эти (слева направо): First Name (Имя), Last Name (Фамилия), Country (Страна), Arrival Date (Ожидаемая дата прибытия) и Status (Статус).

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

В отечественном примере сразу после столбца Participant находится столбец Status. и мы планируем добавить между ними новые столбцы Last Name. Country и Arrival Date .

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

Итог будет приблизительно таким, что Вы видите на рисунке ниже (новые столбцы засунуты слева от выделенных столбцов):

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

  1. Выделите столбец, который требуется разбить. После этого откройте вкладку Data (Эти) Data Tools (Работа с данными) Text to Columns (Текст по столбцам).
  • Откроется диалоговое окно Convert Text to Columns wizard (Мастер распределения текста по столбцам). На первом шаге мастера Вы выбираете формат данных. Так как записи поделены запятыми и пробелами, мы выбираем формат Delimited (С разделителями). Вариант Fixed width (Фиксированной ширины) будет рассмотрен чуть позднее. В случае если все готово, жмите Next (Потом), дабы продолжить.
  • На следующем шаге определяем разделители, каковые находятся в данных, и ограничитель строчков.
  • Настраиваем разделители. В случае если эти поделены одним либо несколькими разделителями, то необходимо выбрать все подходящие варианты в разделе D elimiters (Знаком-разделителем есть) либо ввести собственный вариант разделителя в поле Other (Второй).

    В отечественном примере мы выбираем Space (Пробел) и Comma (Запятая), и ставим галочку наоборот параметра Treat consecutive delimiters as one (Вычислять последовательные разделители одним). Данный параметр окажет помощь избежать лишнего разбиения данных, к примеру, в то время, когда между словами имеется 2 либо более последовательных пробела.

  • Настраиваем ограничитель строчков. Данный параметр может пригодиться, в случае если в столбце, который Вы разбиваете, находятся какие-либо значения, заключённые в кавычки либо в апострофы, и Вы желаете, дабы такие участки текста не разбивались, а рассматривались как цельные значения. К примеру, если Вы выберите в качестве разделителя запятую, а в качестве ограничителя строчков – кавычки (), тогда каждые слова, заключённые в кавычки (к примеру, California, США), будут помещены в одну ячейку. В случае если же в качестве ограничителя строчков установить значение None (Нет), тогда слово California будет помещено в один столбец, а США – в второй.

    • В нижней части диалогового окна находится область Data preview (Пример разбора данных). Перед тем как надавить Next (Потом) будет разумным пролистать это поле и убедиться, что Excel верно распределил все сведенья по столбцам.

  • Осталось сделать всего две вещи – выбрать формат разрешённых и указать, куда поместить поделённые ячейки.

    В разделе Column data format (Формат данных столбца) Вы имеете возможность выбрать формат данных раздельно для каждого столбца, в каковые будут помещены поделённые эти. По умолчанию для всех столбцов задан формат General (Неспециализированный). Мы покинем его без трансформаций для первых трёх столбцов, а для четвёртого столбца установим формат Data (Дата), что логично, поскольку в данный столбец попадут даты прибытия.

    Дабы поменять формат данных для каждого конкретного столбца, выделите его, кликнув по нему в области Data preview (Пример разбора данных), а после этого установите желаемый формат в разделе Column data format (Формат данных столбца).

    На этом же шаге мастера Вы имеете возможность выбрать, в какой столбец поместить поделённые эти. Для этого кликните по иконке выбора диапазона (в терминах Микрософт эта иконка именуется Свернуть диалоговое окно ) справа от поля Destination (Поместить в) и выберите конечный левый столбец из тех, в каковые Вы желаете поместить поделённые эти. К сожалению, нереально импортировать поделённые эти на другой лист либо в другую рабочую книгу, попытка сделать это приведёт к сообщению об неточности выбора конечной ссылки.

    Совет: Если Вы не желаете импортировать какой-то столбец (столбцы), который продемонстрирован в области Data preview (Пример разбора данных), то выделите его и выберите вариант Do not import column (Пропустить столбец) в разделе Column data format (Формат данных столбца).

    1. Надавите Finish (Готово)!

      Разбиваем текст фиксированной ширины

      по нескольким столбцам

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

      К примеру, имеется перечень товаров с ID и наименованием, причем идентификатор товара – это 9 знаков, каковые стоят перед наименованием этого товара:

      Вот что Вам необходимо сделать, дабы разбить таковой столбец на два:

      1. Запустите инструмент Text to Columns (Текст по столбцам), как мы это делали в прошлом примере. На первом шаге мастера выберите параметр Fixed width (Фиксированной ширины) и надавите Next (Потом).
      2. В разделе Data preview (Пример разбора данных) настройте ширину столбцов. Как видно на рисунке ниже, край столбца символизирует вертикальная линия, и дабы задать край следующего столбца, в нужном месте. Двойной щелчок по вертикальной линии удалит край столбца, а вдруг Вам необходимо переместить границу столбца в второе место, вертикальную линию мышью. В действительности, все эти инструкции детально расписаны в верхней части диалогового окна 🙂

        Так как любой ID товара содержит 9 знаков, устанавливаем линию границы столбца на это значение, как продемонстрировано на рисунке выше.

      3. На следующем шаге выберите формат данных и укажите ячейки, куда поместить итог, как это было сделано в прошлом примере, а после этого надавите Finish (Готово).

        4. Разбиваем объединённые ячейки в Excel

        Если Вы объединили пара ячеек на странице Excel и сейчас желаете снова разбить их по отдельным столбцам, откройте вкладку Home (Основная) и в группе команд Alignment (Выравнивание) надавите мелкую тёмную стрелку рядом с кнопкой Merge Center (Объединить и поместить в центре). Потом из выпадающего перечня выберите Unmerge Cells (Отменить объединение ячеек).

        Так объединение ячеек будет отменено, но наслаждение от результата будет сломано тем, что все сведенья останутся в левом столбце. Думаю, Вы додумались, что необходимо опять применять функцию Text to Columns (Текст по столбцам), дабы разбить эти из одного столбца на два либо более столбцов.

        Разделяем эти на пара столбцов в Excel 2013 при помощи мгновенного заполнения

        Если Вы уже обновились до Excel 2013, то имеете возможность воспользоваться преимуществами нового инструмента «Мгновенное заполнение » и вынудить Excel машинально заполнять (в нашем случае – разбивать) эти, при обнаружении определенной закономерности.

        Если Вы ещё не привычны с данной функцией, я попытаюсь коротко растолковать её сущность. Данный инструмент анализирует эти, каковые Вы вводите на рабочий лист, и пробует узнать, откуда они взялись и существует ли в них какая-либо закономерность. Когда «Мгновенное заполнение» выявит Ваши действия и вычислит закономерность, Excel предложит вариант, и последовательность записей в новом столбце покажется практически за мгновение.

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

        В первую очередь, убедитесь, что инструмент «Мгновенное заполнение» включен. Вы отыщете данный параметр на вкладке File (Файл) Options (Параметры) Advanced (Дополнительно) Automatically Flash Fill (Машинально делать мгновенное заполнение).

        Сейчас давайте посмотрим, как возможно машинально разбить эти по ячейкам. Итак, Вы включили инструмент «Мгновенное заполнение», и начинаете вводить с клавиатуры эти, каковые необходимо поместить в отдельные ячейки. По мере ввода Excel будет пробовать выявить шаблон в вводимых значениях, и когда он его выявит, эти машинально будут засунуты в остальные ячейки. Чтобы выяснить, как это трудится, взглянуть на рисунок ниже:

        Как видите, я ввёл лишь несколько имён в столбец B. и «Мгновенное заполнение» машинально заполнило остальные ячейки именами из столбца A. Если вы довольны результатом, Enter. и целый столбец будет заполнен именами. Весьма умный инструмент, не правда ли?

        В случае если «Мгновенное заполнение» включено, но не предлагает никаких вариантов, каковые соответствуют определённому шаблону, Вы имеете возможность запустить данный инструмент вручную на вкладке Data (Эти) Flash Fill (Мгновенное заполнение) либо надавив сочетание клавиш Ctrl+E .

        Как в Excel разбивать ячейки при помощи формул

        Существуют формулы, каковые смогут быть крайне полезны, в то время, когда появляется необходимость разбить ячейки либо столбцы с данными в Excel. В действительности, следующих шести функций хватит как правило – LEFT (ЛЕВСИМВ), MID (ПСТР), RIGHT (ПРАВСИМВ), FIND (ОТЫСКАТЬ), SEARCH (ПОИСК) и LEN (ДЛСТР). Потом в этом разделе я коротко растолкую назначение каждой из этих функций и приведу примеры, каковые Вы сможете применять в собственных книгах Excel.

        Пример 1

        Самая распространённая обстановка, в то время, когда смогут пригодится эти формулы – это необходимость поделить имена из одного столбца по нескольким. На рисунке ниже продемонстрировано, какого именно результата мы пробуем достигнуть:

        Извлекаем имя (столбец First name):

        =LEFT(A2,SEARCH( ,A2,1)-1)

        Извлекаем фамилию (столбец Last name):

        =RIGHT(A2,LEN(A2)-SEARCH( ,A2,1))

        =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК( ;A2;1))

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

        SEARCH (ПОИСК) либо FIND (ОТЫСКАТЬ) – это полностью аналогичные функции, каковые делают поиск позиции определенной текстовой строки в заданной ячейке. Синтаксис формулы:

        =SEARCH(find_text,within_text,[start_num])

        =ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])

        В качестве доводов Вы должны указать: что необходимо отыскать, где необходимо искать, и позицию знака, с которого направляться начать поиск. В отечественном примере SEARCH( ,A2,1) либо ПОИСК( ;A2;1) показывает, что мы желаем отыскать знак пробела в ячейке A2 и начнём поиск с первого знака.

        Замечание: В случае если поиск начинается с первого знака, Вы имеете возможность по большому счету пропустить довод start_num (нач_позиция) в формуле и упростить её до для того чтобы вида:

        =LEFT(A2,SEARCH( ,A2)-1)

        =ЛЕВСИМВ(A2;ПОИСК( ;A2)-1)

        LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) – возвращает левую либо правую часть текста из заданной ячейки соответственно. Синтаксис формулы:

        =LEFT(text,[num_chars])

        =ЛЕВСИМВ(текст;[количество_знаков])

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

        =LEFT(A2,SEARCH( ,A2)-1)

        =ЛЕВСИМВ(A2;ПОИСК( ;A2)-1)

        LEN (ДЛСТР) – вычисляет длину строчка, другими словами количество знаков в заданной ячейке. Синтаксис формулы:

        =LEN(text)

        =ДЛСТР(текст)

        Следующая формула вычисляет количество знаков в ячейке A2 :

        В случае если имена в Вашей таблице содержат отчества либо суффиксы, то потребуются мало более сложные формулы с применением функции MID (ПСТР).

        Источник: office-guru.ru

        Видео №85. Excel. Супер. Как разделить текст в ячейке на столбцы

        Увлекательные записи:
        Подборка статей, которая Вас должна заинтересовать:

        • Объединение и разбиение данных в ячейках в excel с форматированием

          редактирование и Форматирование ячеек в Excel – эргономичный инструмент для наглядного представления информации. Такие возможности программы для работы…

        • Автоподбор в ms excel ширины столбца и высоты строки по содержимому ячеек

          самый быстрый метод добиться, дабы содержимое ячеек отображалось всецело – это применять механизм автоподбора ширины столбца/ высоты строчка по…

        • Как ввести текст в ячейку электронной таблицы

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

        • Как зафиксировать ячейку в формуле excel

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

        • Как работает умножение чисел и ячеек в программе excel

          Excel — программа для работы с таблицами, исходя из этого операции умножения являются базисными базами работы с ней Микрософт Excel — это табличный…

        • Как сцепить ячейки в excel

          на данный момент на сайте Функция «СЦЕПИТЬ» в Excel. Функция «Сцепить» в Excel окажет помощь объединить содержимое ячеек Excel. составить предложение из…

        kapitalbank.ru

        Разделение строки на столбцы в Excel

        Те, кто работает в продажах, в маркетинге или в любом другом направлении, которое использует

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

        Уроки MS Excel

        Условное форматирование в Excel позволяет выделять ячейки различными цветами в зависимости от их содержимого.

        Уроки MS Excel

        Если в Excel необходимо отобразить только записи, удовлетворяющие определённому критерию, то используйте фильтр. Для

        Уроки MS Excel

        В Excel можно сортировать данные по одному или нескольким столбцам. Сортировка может быть выполнена

        Уроки MS Excel

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

        office-guru.ru

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

        

        текст по столбцам в excel

        В разделе Другие языки и технологии на вопрос Как в excel разбить столбец ФИО на 3 столбца Ф,И,О заданный автором Отголосок лучший ответ это Все намного проще - в меню (или вкладке 2007-го) данные есть команда - текст по столбцам - там указывайте разделитель пробел и все.

        Ответ от 22 ответа[гуру]

        Привет! Вот подборка тем с ответами на Ваш вопрос: Как в excel разбить столбец ФИО на 3 столбца Ф,И,О

        Ответ от Миллионер[гуру]написать модуль-функцию. Будет оптимальнее, чем формулами.

        Ответ от Вровень[гуру]Нужно - разбивай.ExcelVBA тебе в помощь.

        Ответ от тракторостроение[гуру]Заменяем пробелы в поле ФИО на символы "|" (можно другой) .Сохраняем эти данные в текстовом формате (указывем выполняя команду Сохранить как...). Экспортируем потом его в Access. При экспорте указываем символ разделителя "|" (или другой) и получаем на выходе таблицу с 3 полями вместо одного. Эту таблицу уже экспортирум обратно в Excel. Другой вариант, используя функции работы со строками (InStr, Len, Left, Mid и др. ) разрабатываем процедуру разделения на VBA.

        Ответ от Байтаков Жаслан[гуру]Пусть в ячейке А2 находится ФИ, например, Белова Ирина.В ячейке С2 (Имя) напиши формулу =ЛЕВСИМВ (A2,НАЙТИ (" ",A2)-1) Пробел между кавычками задает для функции НАЙТИ поиск пробела в ячейке A2. (Аргумент -1 удаляет сам пробел из результата. ) Затем функция ЛЕВСИМВ использует результат для отображения текста, находящегося слева от этого пробела. Этот отображенный текст и есть имя.Поскольку длина фамилий различается, как можно распознать фамилию в Excel? Отличительный признак — расположение после пробела. Чтобы извлечь фамилию, введите в ячейку B2 следующую формулу:=ПРАВСИМВ (A2,ДЛСТР (A2)-НАЙТИ (" ",A2))Эта формула использует функцию ДЛСТР для определения длины текста в ячейке A2: 11 знаков. Затем функция НАЙТИ определяет местоположение пробела (" ") в ячейке A2. Затем Excel вычитает позицию пробела (5 знаков от левого края) из длины текста в ячейке A2. Функция ПРАВСИМВ использует результат вычитания в качестве подсчета знаков для отображения знаков, находящихся справа от пробела. Так, 11 - 5 равняется «Белова» (последние 6 знаков в ячейке) .Но не забудь вычесть еще и отчество после этого преобразования. Это еще пара формул

        Ответ от 2 ответа[гуру]

        Привет! Вот еще темы с нужными ответами:

         

        Ответить на вопрос:

        22oa.ru

        Как можно разделять текст по столбцам в Excel?

        Что вы научитесь делать, посмотрев это видео?

        Ознакомившись с этим видео, вы узнаете, как разделить текст с одного столбца (колонки) на несколько при помощи специального  инструмента «Текст по столбцам» в Excel.

        В предыдущем уроке вы узнали как работать с датами и временами в Excel.

        Описание видео:

        Рассмотрим следующее. Например, у нас есть слипшийся в один столбец текст, который необходимо разделить на несколько отдельных колонок для последующей работы. Конечно же, такой текст можно перенабрать заново, разделяя данные на разные колонки, но это займет очень много времени и сил. Обычно с такой проблемой приходится сталкиваться при различных выгрузках программ, данных и тому подобное. Чтобы разделить текст на несколько различных колонок в Excel существует специальный инструмент, имеющий  название «Текст по столбцам».

        Приступаем к работе.

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

        Откроется специальный трехшаговый «Мастер текстов». По шагам этого мастера можно перемещаться как обычно при помощи кнопок «Далее» и «Назад». Приступим к настраиванию на каждом шаге определенных параметров.

        В первом шаге определяем «Формат данных». Здесь нужно уточнить принцип деления текста на колонки: или по разделителям или по фиксированной ширине. Выбираем формат: с разделителем.

        Второй шаг несколько отличается от предыдущего. Здесь необходимо обозначить, какой символ будет применяться как разделитель. Выбираем «Запятая». Ставим галочку возле «Считать последовательные разделители одним», а в ограничении строк выбираем  кавычки.

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

        Для столбца с датой следует указать в «Формате данных столбца» — «Дата» и установить «МДГ». В данном списке следует указывать не то, что хочется, а то, что сейчас есть в нашем столбце.  Таким образом, мы помогаем и подсказываем Excel правильно определить формат исходных данных.  Аналогично необходимо для столбца с расчетами указываем формат «Текстовый», иначе по умолчанию программа начнет понимать эти числа как числа в математическом смысле. Относительно столбца с суммой, то здесь настройки следующие: нажимаем на кнопку «Подробнее». В появившемся окошке выбираем в графе «Разделитель целой и дробной части» — точку, а в строке «Разделитель разделов» – апостроф.

        Разобравшись со всеми настройками можно нажимать на кнопку  «Готово».  Как видим, наш текст больше не состоит в одном столбце, а расположен корректно и отфильтровано. Мы все сделали верно. Последним шагом в нашей работе остается лишь  подровнять ширину всех колонок. Что мы в итоге получили? Слипшийся текст удачно разделился. Для каждого типа данных отведена своя колонка, то есть в одной указаны фамилии, в следующей – индекс. Далее колонки разделены  на города, улицы, номера домов, а также телефоны, номера счетов, суммы и даты. Все отображается правильно.

        С поставленной задачей мы справились.

        Как создать выпадающий список с картинками в программе Excel, вы узнаете из следующего урока.

         Loading ...

        www.kak-v.com

        Разбиение данных по столбцам в Microsoft Excel 2007

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

        Использовать эту функцию можно следующим образом: выделяем ячейки, данные которых хотим разнести (Рис. 1), выбираем закладку «Данные», функцию «Текст по столбцам». (Рис. 2)

        Рис. 1

        Рис. 2

        В открывшемся окне вам предлагают выбрать, каким образом будут разделятся данные ячейки на несколько ячеек. В нашем случае выбираем что данные есть список значений с разделителями и жмём «Далее» (Рис. 3)

        Рис. 3

        В следующем окне вам предложат выбрать тип разделителя, в нашем случае выбираем что это пробел и жмём «Далее» (Рис. 4)

        Рис. 4

        Далее вы можете указать тип данных в каждом новом столбце и их размещение, в нашем случае указываем везде общий тип данных и размещение по умолчанию (Рис. 5) и жмём «Готово».

        Рис. 5

        Результат вы можете наблюдать на рисунке (Рис. 6)

        Рис. 6

        blog.depit.ru