Как разделить текст в ячейке excel на несколько ячеек

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

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

Для примера рассмотрим данные CSV файла. Каждая подстрока в нем разделена символом «точка с запятой» и помещена в двойные кавычки. Вставьте их в любое место Вашей рабочей книги:

"ФИО";"Дата рождения";"Пол";"Город проживания";"Профессия"
"Иванов Иван Иванович";"27.03.1985";"Мужской";"Воронеж";"Электрик"
"Петров Петр Петрович";"12.03.1976";"Мужской";"Москва";"Геолог"
"Кухтина Елизавета Федоровна";"11.12.1994";"Женский";"Казань";"Врач"

Перед разбивкой данных требуется выделить нужный диапазон, а затем кликнуть по пиктограмме «Текст по столбцам» в области «Работа с данными» на ленте вкладки «Данные».

На экране будет отображено окно мастера распределения текста:

Весь процесс состоит из трех шагов.

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

Выберите пункт «С разделителями» и нажмите кнопку «Далее».

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

Обратите внимание на то, что если разделители идут подряд, то каждый из них образует новый столбец, т.е. 2 таких символа создают 1 пустой столбец, 3 – 2 и т.д. Чтобы избавиться от этого часто нужного действия достаточно поставить галочку на поле «Считать последовательные разделители одним».

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

Имейте в виду то, что символы-разделители и ограничители удаляются из содержимого разделенных данных.

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

Завершающим шагом является назначение форматирования новым колонкам и указания места для их расположения.

Из возможных настроек предоставлено только несколько форматов (типов данных), а поместить распределенный текст можно только на текущем листе.

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Excel: разделяем слипшийся текст

Случается, что содержание одного столбца нужно разбить на несколько. Например:

• слишком обширное описание товара (а нужно, чтоб наименование, характеристики и актуальное наличие были разбиты);
• полный адрес в одном столбце (а нужно, чтоб индекс, область, район и прочие данные были разбиты);
• ФИО в одном столбце (а нужно – в отдельных).

Какая бы ситуация ни сложилась у вас, есть несколько способов разделить слипшийся текст.

Задача №1: Разбить текст по столбцам

Решение: Выделите нужные ячейки, перейдите в меню Данные → Текст по столбцам.

В появившемся окне Мастер текстов (разбора) укажите формат данных:

• с разделителями – в случае, когда содержимое будущих отдельных столбцов разделяется каким-либо символом (укажите, каким именно).

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

• фиксированной ширины – если вы хотите задать произвольную ширину столбца:

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

• Общий – самый универсальный, сохранит данные в исходном виде.
• Дата – для столбцов, где указан день-месяц-год (порядок значений вы также выберете самостоятельно).
• Текстовый – незаменим для ячеек с числовыми данными, которые Excel должен воспринимать как текст. Пример: если столбцу с номерами банковских счетов не назначить текстовый формат, цифры округлятся до 15-ти символов – офис распознает их как числа.

Задача №2: Выборочно выдернуть слова из текста

Для того чтобы сделать это автоматически, воспользуйтесь редактором Visual Basic (вызов горячими клавишами Alt+F11): откройте новый модуль (Insert Module) и вставьте следующий текст функции:

Function Substring(Txt, Delimiter, n) As String

Dim x As Variant

x = Split(Txt, Delimiter)

If n > 0 And n — 1

Substring = x(n — 1)

Else

Substring = «»

End If

End Function

Далее идем в меню Формулы → Вставить функцию, выбираем категорию «Определенные пользователем», применяем синтаксис =SUBSTRING и указываем значения:

• Txt – адрес ячейки с нужным текстом.
• Delimeter – разделяющий знак (запятая, тире, пробел и т.д.).
• N – порядковый номер фрагмента, который нужно «выдернуть».

Задача №3: Разделить текст без пробелов

Если о знаках-разделителях речи даже не идет – то есть текст абсолютно слипшийся (например, ФИО «ПетровскийАндрейИванович»), используйте следующую макрофункцию:

Function CutWords(Txt As Range) As String

Dim Out$

If Len(Txt) = 0 Then Exit Function

Out = Mid(Txt, 1, 1)

For i = 2 To Len(Txt)

If Mid(Txt, i, 1) Like «[a-zа-я]» And Mid(Txt, i + 1, 1) Like «[A-ZА-Я]» Then

Out = Out & Mid(Txt, i, 1) & » »

Else

Out = Out & Mid(Txt, i, 1)

End If

Next i

CutWords = Out

End Function

CutWords аналогично добавляется в редактор Visual Basic, потом выбирается среди функций, определенных пользователем, и автоматически расставляет пробелы перед заглавными буквами.

Читайте нас дальше, чтобы работа с Excel давалась ещё легче.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как разделить текст на две ячейки Excel

Допустим на лист Excel были импортированные данные из другой программы. Из-за несовместимости структуры данных при импорте некоторые значение из разных категорий были внесены в одну ячейку. Необходимо из этой ячейки отделить целые числовые значения. Пример таких неправильно импортированных данных отображен ниже на рисунке:

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

В ячейку B3 введите следующую формулу:

Теперь скопируйте эту формулу вдоль целого столбца:

Выборка чисел из строк в отдельные ячейки.

Описание формулы для разделения текста по ячейкам:

Функция ПСТР возвращает текстовое значение содержащие определенное количество символов в строке. Аргументы функции:

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

С первым аргументом ПСТР все понятно – это ссылка на ячейку A3. Второй аргумент мы вычисляем с помощью функции НАЙТИ(«]»;A3)+2. Она возвращает очередной номер символа первой закрывающейся квадратной скобки в строке. И к этому номеру мы добавляем еще число 2, так как нам нужен номер символа после пробела за квадратной скобкой. В последнем аргументе функция вычисляет какое количество символов будет содержать разделенная строка после разделения, учитывая положение квадратной скобки.

Обратите внимание! Что в нашем примере все исходные и разделенные строки имеют разную длину и разное количество символов. Именно поэтому мы называли такую формулу – гибкой, в начале статьи. Она подходит для любых условий при решении подобного рода задач. Гибкость придает ей сложная комбинация из функций НАЙТИ. Пользователю формулы достаточно определить закономерность и указать их в параметрах функций: будут это квадратные скобки либо другие разделительные знаки. Например, это могут быть пробелы если нужно разделить строку на слова и т.п.

В данном примере функция НАЙТИ во втором аргументе определяет положение относительно первой закрывающейся скобки. А в третьем аргументе эта же функция вычисляет положение нужного нам текста в строке относительно второй открывающийся квадратной скобки. Вычисление в третьем аргументе более сложное и оно подразумевает вычитание одной большей длинны текста от меньшей. А чтобы учитывать еще 2 пробела следует вычитать число 3. В результате чего получаем правильное количество символов в разделенной строке. С помощью такой гибкой формулы можно делать выборку разной длинны разделенного текста из разных длинны исходных строк.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры