Как в excel разбить текст на колонки: Разделение текста по столбцам с помощью функций

Содержание

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

Виктор Бухтеев

4.2K

Обсудить

Личный опыт
#Программы
#Microsoft

8 мин. чтения

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

Как выглядит склеенный текст в Excel

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

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

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

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

  2. Вызовите инструмент «Текст по столбцам».

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

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

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

  6. В моем случае разделителем выступает косая черта (слэш) – нужно отметить галочкой «Другой» и напечатать этот знак в соответствующем поле. Как только вы это сделаете, ниже сразу отобразится результат выполнения операции.

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

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

  9. Вернитесь к листу в Excel и убедитесь в том, что разделение прошло успешно. По надобности поправьте размеры ячеек и перенесите то содержимое, которое было отмечено другим разделителем и не посчиталось автоматически.

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

  1. Выделите столбцы и строки, перейдите на вкладку «Вставка» и нажмите «Таблица».

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

  3. Если нужно, разверните меню «Форматировать как таблицу» и выберите другой вариант визуального оформления.

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

Разделение текста на столбцы сэкономило огромное количество времени. Просто представьте, если бы все эти данные пришлось сортировать вручную. Теперь вы знаете, что встроенный в Microsoft 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 (5 простых способов разделить текст)

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

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

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

В этом учебном пособии вы узнаете, как разделить текст в Excel с помощью функций, формул Text to Columns и Flash Fill и VBA . Метод формул включает в себя разбиение текста по определенному символу. Вот такое сегодня меню.

Давайте разделимся!

Содержание

Использование текста в столбцах

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

  • Выберите данные, которые вы хотите разделить.
  • Перейдите на вкладку Данные и выберите Значок Text to Columns из Data Tools
  • Выберите переключатель Delimited и затем нажмите на Next
  • в разделе , выберите Comma

    • 9

  • , затем выберите 1329
  • , затем Select
  • , затем выберите 132
  • . кнопку Далее .
  • Теперь вам нужно выбрать, где вы хотите разделить текст. Нажмите на поле Destination , затем выберите ячейку назначения на рабочем листе в фоновом режиме, где вы хотите начать разделенный текст.
  • Нажмите кнопку Готово , чтобы закрыть окно Текст в столбцы

Используя запятую для разделения текстовой строки, Текст в столбцы разделил текст из нашего примера на три столбца:

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

Использование функции TRIM для обрезки лишних пробелов

Давайте подробнее рассмотрим вывод Text to Columns . Обратите внимание, как последние два столбца содержат один начальный пробел? Вы можете видеть, что значения в столбцах D и E не выровнены по левому краю на сто процентов:

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

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

=TRIM(C4)

Функция TRIM удаляет из текстовой строки все пробелы, кроме одного пробела между двумя словами. Любые начальные, конечные или лишние пробелы будут удалены из значения ячейки. Мы просто использовали формулу со ссылкой на ячейку, содержащую начальный пробел.

И это очистило для нас лидирующие позиции. Данные — вперед!

Использование формулы для разделения текста в Excel

Мы можем использовать функции Excel для создания формул, которые помогут нам разделить текстовую строку на несколько.

Разделить строку с разделителем

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

Функция ПОДСТАВИТЬ заменяет старый текст в текстовой строке новым текстом.

Функция ПОВТОР повторяет текст заданное количество раз.

Функция ДЛСТР возвращает количество символов в текстовой строке.

Функция MID возвращает заданное количество символов из середины текстовой строки с указанной начальной позицией.

Функция TRIM удаляет из текстовой строки все пробелы, кроме одиночных пробелов между словами.

Теперь давайте посмотрим, как эти функции вместе можно использовать для разделения текста с помощью одной формулы:

=ОТРЕЗАТЬ(СРЕДНЯЯ(ЗАМЕНИТЬ($B5,",",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5) ))

В нашем примере первой ячейкой, для которой мы используем эту формулу, является ячейка B5. Количество символов в ячейке B5, подсчитанное функцией ДЛСТР, равно 49. Функция ПОВТОР повторяет пробелы (обозначаемые в формуле символом » «) в ячейке В5 для количества знаков, предоставленных функцией ДЛСТР, т. е. 49.

Функция ПОДСТАВИТЬ заменяет запятые «,» в B5 с 49 символами пробела, предоставляемыми функцией ПОВТОР. Поскольку в B5 две запятые, одна после названия книги и одна после автора, 49пробелы будут введены после названия книги и 49 пробелов после автора, создавая приличный разрыв между текстом, который мы хотим разделить.

Теперь давайте посмотрим на вычисления для функции MID. Первый бит равен (C$4-1). В строке 4 мы добавили порядковую нумерацию для каждого из столбцов наших категорий. Строка заблокирована в формуле знаком $, поэтому строка не изменяется при копировании формулы. Но мы оставили столбец свободным, чтобы порядковый номер изменился для соответствующих столбцов, используемых в формуле.

В формуле из C4 вычитается 1 (1-1=0), результат умножается на количество символов в B5, т.е. LEN($B5), а затем к выражению добавляется 1. Вычисление начальной позиции в функции MID, т. е. (C$4-1)*LEN($B5)+1, становится равным (1-1)*49+1, что равно 1.

Функция MID возвращает текст из середина B5, начальная позиция равна 1 (это означает, что возвращаемый текст должен начинаться с первого символа), а количество возвращаемых символов равно LEN($B5), т. е. 49персонажи. Поскольку мы добавили 49 пробелов вместо каждой запятой, у нас достаточно места для безопасного возврата только одного фрагмента текста вместе с некоторыми дополнительными пробелами. Результатом до функции MID является «Песнь льда и пламени» с большим количеством пробелов в конце.

Лишние пробелы не проблема. Функция TRIM очищает все лишние пробелы, оставляя одиночные пробелы между словами, и поэтому мы, наконец, возвращаем название книги как «Песнь льда и пламени».

Теперь для следующего столбца и, следовательно, следующей категории расчет начальной позиции в функции MID изменится следующим образом (D$4-1)*LEN($B5)+1. Выражение сводится к (2-1)*49+1, что равно 50. Если функция MID должна возвращать символы, начинающиеся с 50-го символа, со всеми дополнительными пробелами, добавленными функцией REPT, то функция MID будет возвращать следующий шаблон: пробелы автора пробелы.

Начальные и конечные пробелы будут обрезаны функцией TRIM, и результатом будет Джордж Р. Р. Мартин.

«+1» в аргументе начальной позиции функции MID не имеет значения для последующих столбцов, только для первого. Это потому, что без «+1» в расчете первого столбца было бы 0 * 49.который в конечном итоге будет #VALUE! ошибка.

Формула, скопированная по столбцу E, дает нам жанр из объединенного текста в столбце B, и это завершает наш набор.

Разделить строку по определенному символу

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

Теперь это было бы проще, если бы код продукта имел фиксированное количество символов; нам нужно было бы использовать функцию LEFT только для возврата определенного количества символов. Но что в этом веселого?

Мы позволим функции НАЙТИ выполнить за нас небольшую работу по поиску и найти дефис в тексте, чтобы функции ВЛЕВО и ВПРАВО могли вернуть окружающий текст. Это формула с функцией ЛЕВЫЙ для возврата первого извлечения:

=ЛЕВЫЙ(B3,НАЙТИ("-",B3)-1)

Функция НАЙТИ ищет в B3 положение дефиса «-» в текстовой строке, которая равна 6. Затем функция ВЛЕВО возвращает символы, начиная с левого края текста, и число возвращаемых символов равно 6-1. «-1» в конце гарантирует, что возвращаемые символы не включают сам дефис. Вот результаты этой формулы для возврата первого сегмента разделенного текста:

Теперь для второго сегмента текста функция ПРАВИЛЬНО вступает в игру с этой формулой:

=ПРАВИЛЬНО(B3,ДЛСТР(B3)-НАЙТИ("-",B3))

Функция НАЙТИ снова используется для поиска местоположения дефиса в B3, который, как мы знаем, является 6-м символом. Функция ДЛСТР возвращает количество символов в B3, равное 23. Функция ПРАВИЛЬНО извлекает 23-6 символов из B3 и возвращает тип продукта «Bluetooth Speaker». Вот как это работает в нашем примере:

Использование Flash Fill

Функция Flash Fill в Excel автоматически заполняет значения на основе нескольких предоставленных вручную примеров. Простота Flash Fill заключается в том, что вам не нужно запоминать какие-либо формулы, использовать какие-либо мастера или возиться с какими-либо настройками. Если ваши данные непротиворечивы, Flash Fill будет самым быстрым, чтобы понять, что вы пытаетесь сделать. Давайте посмотрим, как использовать Flash Fill для разделения текста и как это работает в нашем примере:

  • Введите первый текст в качестве примера для Flash Fill , чтобы выбрать шаблон, и нажмите Enter
  • На вкладке Home Editing группы и выберите значок Flash Заполните из меню.
  • В качестве альтернативы используйте сочетания клавиш Ctrl + E .
  • Взяв приведенный пример, Flash Fill разделит текст и заполнит столбец по тому же шаблону:
  • Повторите те же шаги для каждого столбца Flash-Filled .

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

Использование функции VBA

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

  • . Если у вас есть вкладка «Разработчик», добавленная на ленту панели инструментов, щелкните вкладку «Разработчик», а затем выберите значок Visual Basic в Группа кода для запуска Visual Basic
  • Вы также можете использовать клавиши Alt + F11 .
  • Откроется редактор Visual Basic :
  • Откройте вкладку Insert и выберите Module из списка. Откроется окно модуля .
  • В окне модуля скопируйте и вставьте следующий код для создания макроса с названием SplitText:
 Sub SplitText() 
 Dim MyArray() As String, Count As Long, i As Variant 
 Для n = от 4 до 16 
 MyArray = Split(Cells(n, 2), ",") 
 Count = 3 
 Для каждого i In MyArray 
 Cells(n, Count) = i 
 Count = Count + 1 
 Next i 
 Next n 
 End Sub

Отредактируйте следующие части кода в соответствии с вашими данными:

  • ‘For n = 4 To 16’ – 4 и 16 представляют первую и последнюю строки набора данных.
  • ‘MyArray = Split(Cells(n, 2), «,»)’ – Запятая, заключенная в двойные кавычки, является разделителем.
  • ‘Count = 3’ — 3 — это номер первого столбца, в котором будут возвращены результирующие данные. в соответствии с предоставленными значениями:

    • Очистите начальные пробелы в столбцах D и E с помощью функции TRIM:

    Теперь давайте отделим активное руководство от его конца. Сегодня вы узнали несколько способов разделения текста в Excel. Если в следующий раз вы обнаружите, что сомневаетесь в своей способности разделять текст, заберите это и будьте готовы попробовать! Мы вернемся с большим количеством превосходства, чтобы наполнить ваши карманы. Освободи место!

    8 способов разделения текста по разделителю в Excel

    Когда данные импортируются в Excel, они могут быть в разных форматах в зависимости от исходного приложения, которое их предоставило.

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

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

    Большим преимуществом использования символа-разделителя является то, что он не зависит от фиксированной ширины текста. Разделитель указывает, где именно разделить текст.

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

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

    Пример данных

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

    Функции Excel для разделения текста

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

    Функция LEFT

    Функция LEFT возвращает количество символов слева от текста.

    Синтаксис 
     = LEFT ( Текст, [Число] )
    • Текст — это текстовая строка, из которой вы хотите извлечь. Это также может быть действительная ссылка на ячейку в книге.
    • Число [Необязательно] — это количество символов, которое вы хотите извлечь из текстовой строки. Значение должно быть больше или равно нулю. Если значение больше длины текстовой строки, будут возвращены все символы. Если значение опущено, то предполагается, что значение равно единице.

    Функция ПРАВО

    Функция ПРАВО возвращает количество символов справа от текста.

    Синтаксис 
     = ПРАВО ( Текст, [Число] )

    Параметры работают так же, как и для функции ЛЕВО , описанной выше.

    Функция НАЙТИ

    Функция НАЙТИ возвращает позицию указанного текста в текстовой строке. Это можно использовать для поиска символа-разделителя. Обратите внимание, что поиск чувствителен к регистру.

    Синтаксис 
     = НАЙТИ (Подтекст, Текст, [Начало])
    • Подтекст — Это текстовая строка, которую вы хотите найти.
    • Текст – Это текстовая строка, которую необходимо найти.
    • Старт [Необязательно] — Начальная позиция для поиска.

    Функция LEN

    Функция LEN определяет длину текстовой строки по количеству символов.

    Синтаксис 
     = ДЛСТР (текст)
    • Текст — это текстовая строка, для которой вы хотите определить количество символов.

    Извлечение данных с помощью функций ВЛЕВО, ВПРАВО, НАЙТИ и ДЛСТР

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

     = НАЙТИ ( ",", B3 )

    Вы используете функцию НАЙТИ , чтобы получить позицию первого символа-разделителя. Это вернет значение 18 .

     = ЛЕВЫЙ ( B3, НАЙТИ( ",", B3 ) - 1 )

    Затем вы можете использовать функцию ЛЕВЫЙ для извлечения первого компонента текстовой строки.

    Обратите внимание, что НАЙТИ получает позицию первого разделителя, но вам нужно вычесть из нее 1, чтобы не включать символ разделителя.

    Это вернет Табби О’Халлаган .

     = ПРАВИЛЬНО (B3, ДЛСТР (B3) - НАЙТИ (",", B3))

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

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

    Функция RIGHT затем усекает все символы до первого разделителя включительно, так что текстовая строка становится все короче и короче по мере нахождения каждого символа-разделителя.

    Это вернет 056 Деннис Парк, Греда, Хорватия, 44273

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

    Повторите для всех разделителей, и это разделит текстовую строку на составные части.

    Функция FILTERXML как динамический массив

    Если вы используете Excel для Microsoft 365, вы можете использовать функцию FILTERXML для разделения текста с выводом в виде динамического массива.

    Вы можете разделить текстовую строку, превратив ее в строку XML, изменив символы-разделители на теги XML. Таким образом, вы можете использовать FILTERXML Функция для извлечения данных.

    XML-теги определяются пользователем, но в этом примере s будет представлять подузел, а t — главный узел.

     = "" & ПОДСТАВИТЬ ( B2, ",", "" ) & ""

    Используйте приведенную выше формулу, чтобы вставить XML-теги в вашу текстовую строку.

     ИмяУлицаГородСтранаПочтовый индекс

    Это вернет приведенную выше формулу в примере.

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

     =ТРАНСП(
    ФИЛЬТРXML(
        "" и
        ЗАМЕНЯТЬ(
            Б3,
            ",",
            ""
        ) & "",
        "//с"
    )
)

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

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

    Функция FILTERXML для разделения текста

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

     = ФИЛЬТРXML (
    "" и
    ЗАМЕНЯТЬ (
        Б3,
        ",",
        ""
    ) & "",
    "//с"
)

    Теперь вы можете разбить строку на разделы, используя приведенную выше формулу FILTERXML .

    Это вернет первую секцию Табби О’Халлаган .

     = ФИЛЬТРXML (
    "" и
    ЗАМЕНЯТЬ (
        Б3,
        ",",
        ""
    ) & "",
    "//с[2]"
)

    Чтобы вернуть следующий раздел, используйте приведенную выше формулу.

    Будет возвращена вторая часть текстовой строки 056 Dennis Park .

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

    Мгновенное заполнение для разделения текста

    Мгновенное заполнение позволяет указать пример того, как вы хотите разделить данные.

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

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

    Используя образец данных, введите Имя в ячейку C2 , затем Табби О’Халлаган в ячейку C3 .

    Флэш-заполнение должно автоматически заполнять оставшиеся имена данных из выборочных данных. Если это не так, вы можете выбрать ячейку C4 и щелкнуть Flash Fill 9.0185 в группе Data Tools на вкладке Data ленты Excel.

    Аналогичным образом можно добавить Улица в ячейку D2 , Город в ячейку E2 , Страна в ячейку F2 и Почтовый индекс в ячейку .

    Выберите последующие ячейки (от D2 до G2) по отдельности и щелкните значок Быстрое заполнение. Остальные текстовые компоненты будут размещены в этих столбцах.

    Текст в столбцы Команда для разделения текста

    Эту функцию Excel можно использовать для разделения текста в ячейке на разделы на основе символа-разделителя.

    1. Выберите весь диапазон выборочных данных (B2:B12).
    2. Щелкните вкладку Данные на ленте Excel.
    3. Щелкните значок Text to Columns в группе Data Tools на ленте Excel, и появится мастер, который поможет вам настроить способ разделения текста.
    4. Выберите с разделителями на кнопках выбора.
    5. Нажмите кнопку Далее .
    1. Выберите Запятая в качестве разделителя и снимите все остальные разделители.
    2. Нажмите кнопку Далее .
    3. В окне предварительного просмотра данных будет показано, как будут разделены ваши данные. Выберите место для размещения вывода.
    4. Нажмите кнопку Finish .

    Теперь ваши данные будут отображаться в столбцах на вашем рабочем листе.

    Преобразование данных в файл CSV

    Это будет работать только с запятыми в качестве разделителей, поскольку файл CSV (значения, разделенные запятыми) зависит от запятых для разделения значений.

    Откройте Блокнот, скопируйте и вставьте в него данные образца. Вы можете открыть Блокнот, введя Блокнот в поле поиска слева от панели задач Windows или найдя его в списке приложений.

    Скопировав данные в Блокнот, сохраните их, используя Файл ➜ Сохранить как из меню. Введите имя файла с суффиксом .csv, например. Разделить данные.csv.

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

    VBA для разделения текста

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

    Для доступа к редактору Visual Basic (VBE) используйте Alt + F11.

     Подразделение текста()
Dim MyArray() как строка, считать как длинный, я как вариант
Для n = 2 до 12
MyArray = Разделить (Ячейки (n, 2), ",")
Количество = 3
Для каждого i в MyArray
Ячейки (n, количество) = я
Счет = Счет + 1
Далее я
Следующий n
End Sub

    Нажмите Вставьте в строку меню и нажмите Module . Для модуля появится новая панель. Вставьте приведенный выше код.

    Этот код создает одномерный массив с именем MyArray . Затем он перебирает образцы данных (строки со 2 по 12) и использует функцию разделения VBA для заполнения Мой Массив .

    Функция разделения использует разделитель-запятую, так что каждый раздел текста становится элементом массива.

    Для переменной счетчика установлено значение 3, которое представляет столбец C, который будет первым столбцом для отображения данных разделения.

    Затем код выполняет итерацию по каждому элементу массива и заполняет каждую ячейку элементом. Ссылки на ячейки основаны на n для строки и Count для столбца.

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

    Power Query для разделения текста

    Power Query в Excel позволяет разделить столбец на разделы с помощью символа-разделителя.

    Похожие сообщения:

    • Введение в power query
    • Советы и приемы Power query
    • Введение в power query М-код

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

    Щелкните вкладку Данные на ленте Excel, а затем щелкните Получить данные в группе Получить и преобразовать данные на ленте.

    Щелкните Из файла в первом раскрывающемся списке, а затем щелкните Из рабочей книги во втором раскрывающемся списке.

    Отобразится файловый браузер. Найдите файл данных образца (файл, который вы открыли) и нажмите OK .

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

    Разверните дерево данных на левой панели, чтобы отобразить предварительный просмотр существующих данных.

    Нажмите Transform Data , и откроется редактор Power Query.

    Убедитесь, что выделен единственный столбец с данными. Щелкните значок Split Column в группе Transform на ленте. Нажмите By Delimiter в раскрывающемся списке.

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

    Щелкните OK , и данные будут преобразованы в отдельные столбцы.

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

    Вычисляемый столбец Power Pivot для разделения текста

    Вы можете использовать Power Pivot для разделения текста с помощью вычисляемых столбцов.

    Щелкните вкладку Power Pivot на ленте Excel, а затем щелкните значок Добавить значок модели данных в группу Таблицы .

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

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

    Нажмите OK , и появится экран предварительного просмотра.

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

     = ЛЕВЫЙ ( [Столбец1], НАЙТИ ( ",", [Столбец1] ) - 1 )

    В строке формул вставьте приведенную выше формулу.

    Работает аналогично функциям, описанным в способе 1 этой статьи.

    Эта формула предоставит компонент Имя в текстовой строке.

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

     = ЛЕВЫЙ (
    RIGHT ([Столбец1], LEN ([столбец1]) — LEN ([Вычисляемый столбец 1]) — 1),
    НАХОДИТЬ (
        ",",
        RIGHT ([Столбец1], LEN ([столбец1]) - LEN ([Вычисляемый столбец 1]) - 1)
    ) - 1
)

    Вставьте приведенную выше формулу в строку формул.

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

    Это обеспечит компонент Street в текстовой строке.

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

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

    Щелкните первую ячейку в столбце Добавить столбец и введите формулу =1 в строке формул.

    Это добавит значение 1 до конца этого столбца. Щелкните значок Pivot Table на вкладке Home на ленте.

    Нажмите Сводная таблица во всплывающем меню. Укажите местоположение вашей сводной таблицы в первом всплывающем окне и нажмите OK . Если панель Поля сводной таблицы не отображается автоматически, щелкните правой кнопкой мыши скелет сводной таблицы и выберите Показать список полей .