На первом этапе приведенной выше формулы мы заменили третий пробел символом «|». Причина выбора этого символа в том, что мы знаем, что он не встречается в исходном тексте.
Три этапа, показанные в ячейках A2–A4 приведенной выше электронной таблицы, возвращают левую часть исходной текстовой строки до третьего пробела. Если вы хорошо разбираетесь в формулах Excel, вы можете объединить эти три этапа в одну формулу, как показано ниже:
Разбиение текста из одной ячейки на несколько ячеек — это задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel с помощью функции Текст в столбец, надстроек Быстрая заливка и Разделить имена. в. Сегодня мы подробно рассмотрим, как можно разбивать строки с помощью формул и функции Разделить текст.
Для разделения строки в Excel обычно используются функции ВЛЕВО, ВПРАВО или СРЕДНЯЯ в сочетании с НАЙТИ или ПОИСК. На первый взгляд некоторые формулы могут показаться сложными, но на самом деле логика довольно проста, и следующие примеры дадут вам некоторые подсказки.
При разделении ячеек в Excel важно найти положение разделителя в текстовой строке. В зависимости от вашей задачи, это можно сделать с помощью SEARCH без учета регистра или FIND с учетом регистра. Получив позицию разделителя, используйте функцию ВПРАВО, ВЛЕВО или СРЕДНЯЯ, чтобы извлечь соответствующую часть текстовой строки. Для лучшего понимания рассмотрим следующий пример.
В этой формуле мы используем функцию Excel MID для извлечения текста из A2: MID(text, start_num, num_chars).
Подобным образом вы можете разделить столбец по любому другому символу. Все, что вам нужно сделать, это заменить «-» на необходимый разделитель, например, пробел («»), косую черту («/»), двоеточие («;»), точку с запятой («;») и т. д. < /p>
Совет. В приведенных выше формулах +1 и -1 соответствуют количеству символов в разделителе. В данном примере это дефис (1 символ). Если ваш разделитель состоит из 2 символов, например. запятую и пробел, затем укажите только запятую (",") для функции ПОИСК и используйте +2 и -2 вместо +1 и -1.
Чтобы разделить текст по пробелам, используйте формулы, подобные тем, что были показаны в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция CHAR для ввода символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу.
Возьмите формулы из предыдущего примера и замените дефис ("-") на CHAR(10), где 10 – код ASCII для перевода строки.
Начнем с того, что не существует универсального решения, которое работало бы для всех буквенно-цифровых строк. Какую формулу использовать, зависит от конкретного шаблона строки. Ниже вы найдете формулы для двух распространенных сценариев.
Предположим, у вас есть столбец строк с текстом и числами, где число всегда следует за текстом. Вы хотите разбить исходные строки, чтобы текст и числа отображались в отдельных ячейках, например:
Результат может быть достигнут двумя различными способами.
Самый простой способ разделить текстовую строку, в которой после текста следует число, заключается в следующем:
Чтобы извлечь числа, вы ищете в строке все возможные числа от 0 до 9, получаете общее число и возвращаете столько же символов с конца строки.
Чтобы извлечь текст, вы вычисляете, сколько текстовых символов содержит строка, вычитая количество извлеченных цифр (C2) из общей длины исходной строки в A2. После этого вы используете функцию LEFT, чтобы вернуть столько символов с начала строки.
Где A2 — исходная строка, а C2 — извлеченное число, как показано на снимке экрана:
Альтернативным решением может быть использование следующей формулы для определения положения первой цифры в строке:
Подробное объяснение логики формулы можно найти здесь.
После того, как положение первой цифры найдено, вы можете разделить текст и числа, используя очень простые формулы ВЛЕВО и ВПРАВО.
Где A2 — исходная строка, а B2 — позиция первого числа.
Чтобы избавиться от вспомогательного столбца, содержащего позицию первой цифры, вы можете встроить формулу MIN в функции LEFT и RIGHT:
Если вы разделяете ячейки, в которых после числа появляется текст, вы можете извлечь числа с помощью следующей формулы:
Формула аналогична той, что обсуждалась в предыдущем примере, за исключением того, что вы используете функцию LEFT вместо RIGHT, чтобы получить число с левой стороны строки.
После получения чисел извлеките текст, вычитая количество цифр из общей длины исходной строки:
Где A2 — исходная строка, а B2 — извлеченное число, как показано на снимке экрана ниже:
Совет. Чтобы получить число из любой позиции в текстовой строке, используйте либо эту формулу, либо инструмент «Извлечь».
Вот как вы можете разделить строки в Excel, используя различные комбинации различных функций. Как видите, формулы далеко не очевидны, поэтому вы можете загрузить образец книги Excel с разделенными ячейками, чтобы изучить их поближе.
Если вам не нравится разбираться в хитросплетениях формул Excel, вам может понравиться визуальный метод разделения ячеек в Excel, который демонстрируется в следующей части этого руководства.
Альтернативный способ разделения столбца в Excel — использование функции "Разделить текст", входящей в состав Ultimate Suite for Excel, которая предоставляет следующие возможности:
Чтобы было понятнее, давайте подробнее рассмотрим каждый вариант по отдельности.
Выбирайте этот параметр, когда хотите разделить содержимое ячейки при каждом появлении указанного символа.
- Предполагая, что у вас установлен Ultimate Suite, выберите ячейки, которые необходимо разделить, и щелкните значок «Разделить текст» на вкладке Данные Ablebits.
- Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу "Разделить по символам" и выберите один из предустановленных разделителей или введите любой другой символ в поле Пользовательский.
- Выберите, следует ли разбивать ячейки на столбцы или строки.
- Просмотрите результат в разделе Предварительный просмотр и нажмите кнопку "Разделить".
Совет. Если в ячейке может быть несколько последовательных разделителей (например, более одного пробела), установите флажок Обрабатывать последовательные разделители как один.
Готово! Задача, для которой требовалось 3 формулы и 5 различных функций, теперь выполняется всего за пару секунд и нажатие кнопки.
Разделить ячейки по строке
Этот параметр позволяет разбивать строки, используя любую комбинацию символов в качестве разделителя. Технически вы разбиваете строку на части, используя одну или несколько разных подстрок в качестве границ каждой части.
Например, чтобы разделить предложение на союзы "и" и "или", разверните группу "Разделить по строкам" и введите строки-разделители, по одной на каждую. строка:
В результате исходная фраза разделяется при каждом появлении каждого разделителя:
Совет. Символы «или», а также «и» часто могут быть частью таких слов, как «апельсин» или «Андалусия», поэтому не забудьте ввести пробел до и после и и или< /em> для предотвращения разделения слов.
А вот еще один пример из жизни. Предположим, вы импортировали столбец дат из внешнего источника, который выглядит следующим образом:
Этот формат не является стандартным для Excel, поэтому ни одна из функций даты не распознает элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Разделить по строкам:
- Точка (.) для разделения дня, месяца и года
- Двоеточие (:) для разделения часов и минут
- Пробел для разделения даты и времени
Нажмите кнопку Разделить, и вы сразу же получите результат:
Разделить ячейки по маске (шаблону)
Разделение ячейки по маске означает разделение строки на основе шаблона.
Этот параметр очень удобен, когда вам нужно разбить список однородных строк на несколько элементов или подстрок. Сложность заключается в том, что исходный текст не может быть разделен при каждом появлении данного разделителя, а только в некоторых конкретных случаях. Следующий пример облегчит понимание.
Предположим, у вас есть список строк, извлеченных из некоторого файла журнала:
Что вам нужно, так это иметь дату и время, если они есть, код ошибки и сведения об исключении в 3 отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем есть пробелы, которые должны отображаться в одном столбце, а также есть пробелы в тексте исключения, которые также должны отображаться в одном столбце.
Решение заключается в разбиении строки по следующей маске: *ERROR:*Exception:*
Где звездочка (*) обозначает любое количество символов.
Двоеточие (:) включено в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
А теперь разверните раздел Разделить по маске на панели Разделить текст, введите маску в поле Введите разделители и нажмите Разделить:
Результат будет выглядеть примерно так:
Примечание. Разделение строки по маске чувствительно к регистру. Поэтому обязательно вводите символы в маске точно так же, как они появляются в исходных строках.
Большим преимуществом этого метода является гибкость. Например, если все исходные строки имеют значения даты и времени и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:
В переводе на обычный английский язык маска указывает надстройке разделить исходные строки на 4 части:
- Все символы до первого пробела в строке (дата)
- Символы между первым пробелом и словом ОШИБКА: (время)
- Текст между ERROR: и Exception: (код ошибки)
- Все, что идет после Exception: (текст исключения)
Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вам интересно попробовать, ознакомительная версия доступна для скачивания ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Когда данные импортируются в Excel, они могут быть в разных форматах в зависимости от исходного приложения, которое их предоставило.
Например, он может содержать имена и адреса клиентов или сотрудников, но все это заканчивается непрерывной текстовой строкой в одном столбце рабочего листа, вместо того, чтобы быть разделенным на отдельные столбцы, например. название, улица, город.
Вы можете разделить данные, используя общий символ-разделитель. Символ-разделитель обычно представляет собой запятую, табуляцию, пробел или точку с запятой. Этот символ разделяет каждый фрагмент данных в текстовой строке.
Большим преимуществом использования символа-разделителя является то, что он не зависит от фиксированной ширины текста. Разделитель указывает, где именно разделить текст.
Возможно, вам придется разделить данные, потому что вы хотите отсортировать данные, используя определенную часть адреса, или иметь возможность фильтровать по определенному компоненту. Если данные используются в сводной таблице, вам может потребоваться указать имя и адрес в качестве разных полей внутри нее.
В этой статье показано восемь способов разделения текста на составные части с использованием символа-разделителя для обозначения точек разделения.
Пример данных
Приведенные выше образцы данных будут использоваться во всех следующих примерах. Загрузите файл примера, чтобы получить образец данных, а также различные решения для извлечения данных на основе разделителей.
Функции Excel для разделения текста
Существует несколько функций Excel, которые можно использовать для разделения текста внутри ячейки и управления им.
ЛЕВАЯ функция
Функция ВЛЕВО возвращает количество символов слева от текста.
Синтаксис
- Текст — это текстовая строка, из которой вы хотите извлечь данные. Это также может быть действительная ссылка на ячейку в книге.
- Число [Необязательно] — это количество символов, которое вы хотите извлечь из текстовой строки. Значение должно быть больше или равно нулю. Если значение больше длины текстовой строки, будут возвращены все символы. Если значение опущено, предполагается, что оно равно единице.
ВПРАВО
Функция ПРАВИЛЬНО возвращает количество символов справа от текста.
Синтаксис
Параметры работают так же, как и для функции ВЛЕВО, описанной выше.
Функция НАЙТИ
Функция НАЙТИ возвращает позицию указанного текста в текстовой строке. Это можно использовать для поиска символа-разделителя. Обратите внимание, что при поиске учитывается регистр.
Синтаксис
- SubText — это текстовая строка, которую вы хотите найти.
- Текст — это текстовая строка, которую необходимо найти.
- Начало [Необязательно] — начальная позиция для поиска.
ДЛСТР
Функция ДЛСТР определяет длину текстовой строки по количеству символов.
Синтаксис
- Текст — это текстовая строка, для которой вы хотите определить количество символов.
Извлечение данных с помощью функций LEFT, RIGHT, FIND и LEN
Используя первую строку (B3) образца данных, эти функции можно комбинировать для разделения текстовой строки на разделы с помощью символа-разделителя.
Вы используете функцию НАЙТИ, чтобы получить позицию первого символа-разделителя. Это вернет значение 18.
Затем вы можете использовать функцию LEFT для извлечения первого компонента текстовой строки.
Обратите внимание, что НАЙТИ получает позицию первого разделителя, но вам нужно вычесть из нее 1, чтобы не включать символ разделителя.
Это вернет Табби О’Халлаган.
Сложнее получить следующие компоненты текстовой строки. Вам нужно удалить первый компонент из текста, используя приведенную выше формулу.
Эта формула берет длину исходного текста, находит первую позицию разделителя, которая затем вычисляет, сколько символов осталось в текстовой строке после этого разделителя.
Затем функция RIGHT усекает все символы до первого разделителя включительно, так что текстовая строка становится все короче и короче по мере нахождения каждого символа-разделителя.
Это вернет 056 Деннис Парк, Греда, Хорватия, 44273
Теперь вы можете использовать НАЙТИ, чтобы найти следующий разделитель, и функцию ВЛЕВО, чтобы извлечь следующий компонент, используя тот же метод, что и выше.
Повторите для всех разделителей, и это разделит текстовую строку на составные части.
Функция FILTERXML как динамический массив
Если вы используете Excel для Microsoft 365, вы можете использовать функцию FILTERXML для разделения текста с выводом в виде динамического массива.
Вы можете разделить текстовую строку, превратив ее в строку XML, изменив символы-разделители на теги XML. Таким образом, вы можете использовать функцию FILTERXML для извлечения данных.
Теги XML определяются пользователем, но в этом примере s будет представлять подузел, а t — основной узел.
Используйте приведенную выше формулу, чтобы вставить теги XML в текстовую строку.
Это вернет приведенную выше формулу в примере.
Обратите внимание, что за каждым из определенных узлов следует закрывающий узел с обратной косой чертой. Эти теги XML определяют начало и конец каждого раздела текста и фактически действуют так же, как разделители.
Вышеприведенная формула вставляет теги XML в исходную строку, а затем использует их для разделения элементов на массив.
Как видно выше, каждый элемент массива будет помещен в отдельную ячейку. Использование функции ТРАНСП приводит к тому, что массив перемещается по горизонтали, а не по вертикали.
Функция FILTERXML для разделения текста
Если в вашей версии Excel нет динамических массивов, вы все равно можете использовать функцию FILTERXML для извлечения отдельных элементов.
Теперь вы можете разбить строку на разделы, используя приведенную выше формулу FILTERXML.
Это вернет первый раздел Tabbie O’Hallagan.
Чтобы вернуться к следующему разделу, используйте приведенную выше формулу.
Это вернет вторую часть текстовой строки 056 Dennis Park.
Вы можете использовать этот же шаблон для возврата любой части образца текста, просто соответствующим образом измените [2], найденный в формуле.
Быстрая заливка для разделения текста
Flash Fill позволяет указать пример того, как вы хотите разделить данные.
Дополнительные сведения см. в этом руководстве по использованию флэш-заполнения для очистки данных.
Затем вы выбираете первую ячейку, в которой хотите разделить данные, и нажимаете «Мгновенное заполнение». Excel заполнит оставшиеся строки из вашего примера.
Используя образец данных, введите Имя в ячейку C2, а затем Табби О'Халлаган в ячейку C3.
Флэш-заполнение должно автоматически заполнять оставшиеся имена данных из примера данных. Если это не так, вы можете выбрать ячейку C4 и щелкнуть значок «Быстрая заливка» в группе «Инструменты данных» на вкладке «Данные» на ленте Excel.
Аналогично вы можете добавить улицу в ячейку D2, город в ячейку E2, страну в ячейку F2 и почтовый индекс в ячейку G2.
Выберите последующие ячейки (от D2 до G2) по отдельности и нажмите значок "Быстрая заливка". Остальные текстовые компоненты будут размещены в этих столбцах.
Команда «Текст в столбцы» для разделения текста
Эту функцию Excel можно использовать для разделения текста в ячейке на разделы на основе символа-разделителя.
- Выберите весь диапазон выборочных данных (B2:B12).
- Нажмите вкладку "Данные" на ленте Excel.
- Нажмите значок «Текст в столбцы» в группе «Инструменты данных» на ленте Excel, и появится мастер, который поможет вам настроить разделение текста.
- Выберите «С разделителями» на кнопках выбора.
- Нажмите кнопку "Далее".
- Выберите запятую в качестве разделителя и снимите все остальные разделители.
- Нажмите кнопку "Далее".
- В окне предварительного просмотра данных будет показано, как будут разделены ваши данные. Выберите место для размещения вывода.
- Нажмите кнопку "Готово".
Теперь ваши данные будут отображаться в столбцах на вашем листе.
Преобразовать данные в файл CSV
Это будет работать только с запятыми в качестве разделителей, поскольку файл CSV (значения, разделенные запятыми) зависит от запятых для разделения значений.
Откройте Блокнот, скопируйте и вставьте в него образцы данных. Вы можете открыть Блокнот, введя Блокнот в поле поиска слева от панели задач Windows или найдя его в списке приложений.
После того как вы скопировали данные в Блокнот, сохраните их, выбрав в меню Файл ➜ Сохранить как. Введите имя файла с суффиксом .csv, например. Разделить данные.csv.
Затем вы можете открыть этот файл в Excel. Выберите CSV-файл в раскрывающемся списке типов файлов браузера и нажмите «ОК». Ваши данные будут автоматически отображаться с каждым компонентом в отдельных столбцах.
VBA для разделения текста
VBA – это язык программирования, лежащий в основе Excel и позволяющий вам писать собственный код для управления данными или даже для создания собственных функций.
Для доступа к редактору Visual Basic (VBE) используйте Alt + F11 .
Нажмите «Вставить» в строке меню, затем нажмите «Модуль». Для модуля появится новая панель. Вставьте приведенный выше код.
Этот код создает одномерный массив с именем MyArray. Затем он перебирает образцы данных (строки со 2 по 12) и использует функцию разделения VBA для заполнения массива MyArray.
Функция разделения использует разделитель-запятую, поэтому каждый раздел текста становится элементом массива.
Переменной-счетчику присвоено значение 3, которое представляет столбец C, который будет первым столбцом для отображения данных разделения.
Затем код выполняет итерацию по каждому элементу массива и заполняет каждую ячейку элементом. Ссылки на ячейки основаны на n для строки и Count для столбца.
Переменная Count увеличивается в каждом цикле, чтобы данные заполняли всю строку, а затем уменьшались.
Power Query для разделения текста
Power Query в Excel позволяет разделить столбец на разделы с помощью символа-разделителя.
Первое, что нужно сделать, это определить источник данных, который представляет собой образцы данных, которые вы ввели в свой рабочий лист Excel.
Перейдите на вкладку "Данные" на ленте Excel, а затем нажмите "Получить данные" в группе "Получить и преобразовать данные" на ленте.
Нажмите «Из файла» в первом раскрывающемся списке, а затем нажмите «Из книги» во втором раскрывающемся списке.
Откроется браузер файлов. Найдите файл образца данных (файл, который вы открыли) и нажмите OK.
Появится всплывающее окно навигации, показывающее все рабочие листы в вашей книге. Нажмите на рабочий лист с образцами данных, и это покажет предварительный просмотр данных.
Разверните дерево данных на левой панели, чтобы отобразить предварительный просмотр существующих данных.
Нажмите «Преобразовать данные», и откроется редактор Power Query.
Убедитесь, что выделен единственный столбец с данными. Щелкните значок «Разделить столбец» в группе «Преобразование» на ленте. Нажмите «По разделителю» в раскрывающемся списке.
Появится всплывающее окно, в котором можно выбрать разделитель. По умолчанию используется запятая.
Нажмите "ОК", и данные будут преобразованы в отдельные столбцы.
Нажмите «Закрыть и загрузить» в группе «Закрыть» на ленте, и в вашу книгу будет добавлен новый рабочий лист с таблицей данных в новом формате.
Вычисляемый столбец Power Pivot для разделения текста
Вы можете использовать Power Pivot для разделения текста с помощью вычисляемых столбцов.
Нажмите вкладку Power Pivot на ленте Excel, а затем щелкните значок "Добавить в модель данных" в группе "Таблицы".
Ваши данные будут автоматически обнаружены, и во всплывающем окне будет показано местоположение. Если это не правильное местоположение, его можно переустановить здесь.
Не устанавливайте флажок Моя таблица имеет заголовки во всплывающем окне, так как мы также хотим разделить заголовок.
Нажмите OK, и отобразится экран предварительного просмотра.
Щелкните правой кнопкой мыши заголовок столбца данных (Столбец 1) и выберите «Вставить столбец» во всплывающем меню. Будет вставлен вычисляемый столбец, в который можно ввести формулу.
Вставьте приведенную выше формулу в строку формул.
Это работает аналогично функциям, описанным в способе 1 этой статьи.
Эта формула предоставит компонент имени в текстовой строке.
Вставьте еще один вычисляемый столбец, используя ту же методологию, что и первый вычисляемый столбец.
Вставьте приведенную выше формулу в строку формул.
Это сложная формула, и вы можете разбить ее на разделы, используя несколько вычисляемых столбцов.
Это обеспечит компонент "Улица" в текстовой строке.
Вы можете продолжить изменять формулу, чтобы создать вычисляемые столбцы для всех остальных компонентов текстовой строки.
Проблема со сводной таблицей заключается в том, что ей нужно не только текстовое значение, но и числовое. Поскольку данные образца являются только текстовыми, необходимо добавить числовое значение.
Нажмите на первую ячейку в столбце "Добавить столбец" и введите формулу =1 в строке формул.
Это добавит значение 1 до конца этого столбца. Щелкните значок сводной таблицы на вкладке "Главная" ленты.
Нажмите «Сводная таблица» во всплывающем меню. Укажите расположение вашей сводной таблицы в первом всплывающем окне и нажмите OK. Если панель «Поля сводной таблицы» не отображается автоматически, щелкните правой кнопкой мыши скелет сводной таблицы и выберите «Показать список полей».
Нажмите «Вычисляемые столбцы» в списке полей и поместите их в окно «Строки».
наша сводная таблица теперь будет отображать отдельные компоненты текстовой строки.
Выводы
Работа с данными, разделенными запятыми или другими разделителями, может стать большой проблемой, если вы не знаете, как извлечь каждый элемент в отдельную ячейку.
К счастью, в Excel есть довольно много опций, которые помогут справиться с этой распространенной задачей.
Вы можете разделить текстовую строку с помощью функции "Текст в столбцы" в Excel.
Выделите текст, который хотите разделить.
На ленте выберите Данные>Текст в столбцы.
Оставьте выбранным параметр "С разделителями" и нажмите "Далее".
Выберите «Пробел» в качестве разделителя и измените классификатор текста на «Нет»
Нажмите "Далее".
На этом шаге вы можете изменить тип данных столбца. Оставьте их как есть и нажмите "Готово".
Разделить текстовую строку пробелом
Чтобы разделить текстовую строку по пробелу или запятой, мы можем использовать функции НАЙТИ, ВЛЕВО, СРЕДНЕЕ и ПРАВО.
Функции ВЛЕВО и НАЙТИ
Во-первых, мы можем найти фамилию с помощью функций ВЛЕВО и НАЙТИ.
Читайте также:
