📌 Функция FILTERXML: разделяем текст по столбцам. Пример решения

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

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

Для удобства поиска нужного материала у нас на канале есть удобный рубрикатор, а все видео предыдущих уроков также доступны на youtube (ссылка внизу поста и в описании канала)

Функция FILTERXML: разделяем текст по столбцам. Пример решения

📢 Файл с примером размещен в конце статьи 🔽

🔔 В данном примере будем использовать встроенные функции: ТРАНСП, ФИЛЬТР.XML и ПОДСТАВИТЬ

🔘 Функция ТРАНСП

Функция ТРАНСП возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функцию необходимо вводить как формулу массива (используя сочетание Ctrl+ SHIFT+ ENTER) в диапазон, содержащий столько же строк и столбцов, что и аргумент массив. Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.

🔘 Функция ФИЛЬТР.XML

Функция ФИЛЬТР.XML возвращает определенные данные из XML-содержимого с помощью указанного xpath.

🔘 Функция ПОДСТАВИТЬ

Подставляет значение аргумента "нов_текст" вместо значения аргумента "стар_текст" в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке

▶️ Возврат всех элементов

Использование перечисленных выше функций позволит разделить текст на отдельные ячейки:

Приведенная выше формула разделит полное ФИО в ячейке A1 на Имя, Фамилию и Отчество

📝 Пояснение к формуле:

• Используя функцию ПОДСТАВИТЬ заменяем пробелы в ячейке A1 на </s><s>, чтобы преобразовать строку в XML-формат;
• Используя функцию ФИЛЬТР.XML, разбираем полученную строку XML и извлекаем значения, находящиеся между тегами <s>;
• (ФИЛЬТР.XML("<t><s>" & ПОДСТАВИТЬ( A1;" ";"</s><s>")&"</s></t>";"//s") возвращает массив значений, где //s фильтр извлекает каждый из элементов, заключенных в s-теги, а & используется для объединения текста;
• Функция ТРАНСП меняет ориентацию массива значений.

🔔 Обращаем внимание! Приведенная формула является формулой массива. Формулу необходимо вводить используя сочетание:

▶️ Возврат i-го элемента в s-теге

Чтобы извлечь определенное слово потребуется немного изменить приведенную выше формулу

🔔 Аналогично, приведенной выше формуле используем фильтр //s[i] для возврата i элемента в s-теге. Если:

• //s[1] - фильтр вернет только 1-й элемент в s-теге (получаем только Фамилию):

• //s[2] - фильтр вернет только 2-й элемент в s-теге (получаем только Имя):

• //s[3] - фильтр вернет только 3-й элемент в s-теге (получаем только Отчество):

См. так же другие способы: Как разделить текст по столбцам 🔽

Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel

🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:

Будем рады видеть вас в числе подписчиков

⏩ СКАЧАТЬ ПРИМЕР

Если вы нашли предоставленный материал полезным, будем благодарны за вашу поддержку