Многие слышали о том, что такое регулярные выражения, но не все знают, что они поддерживаются Microsoft Excel. Регулярные выражения позволяют во много раз ускорить работу с текстом, находить самые замысловатые шаблоны и решать самые сложные поисковые задачи. Единственная проблема в том, что для их использования в Excel требуется знание VBA.
Почему Microsoft не включила их в функции электронных таблиц и будет ли это когда-либо, неясно и неизвестно.
Но с дополнением! SEMTools эти знания не нужны. С другой стороны, минимальное понимание синтаксиса регуляров позволит вам легко решать проблемы, которые практически невозможно решить с помощью стандартных функций, или требуются формулы огромной длины.
Для поддержки регулярных выражений при подключении надстройки! SEMTools в Excel будут работать 3 функции: REGEXMATCH, REGEXEXTRACT и REGEXREPLACE.
Их синтаксис и принцип работы аналогичны таблицам Google. Следовательно, формулы, написанные в Excel, будут полностью совместимы с электронными таблицами Google.
= REGEXMATCH («текст»; «Схема RegEx для поиска»)
MATCH REGEX возвращает True или False (TRUE или FALSE в английской версии Excel) в зависимости от того, соответствует ли текст шаблону или нет.
= REGEXEXTRACT («текст»; «шаблон поиска RegEx»)
REGEXEXTRACT извлекает первый фрагмент текста, соответствующий шаблону. Небольшое отличие от Google Spreadsheets: если в поисковом тексте нет такого фрагмента, Spreadsheets вернет ошибку, а в надстройке будет возвращена пустая строка.
= REGEXREPLACE («текст»; «Схема RegEx для поиска»; «текст, которым мы заменяем найденное»)
Я не буду скупиться на примерах, чтобы показать вам все возможности регулярных выражений, поскольку они действительно великолепны. Я надеюсь, что эта статья послужит руководством и призывом активно использовать их силу. От простого к сложному.
В надстройку! SEMTools был добавлен ряд быстрых подпрограмм, позволяющих обычным пользователям Excel использовать все возможности регулярных выражений. Все приведенные ниже примеры будут показаны с их использованием.
. + \ d \ d.+
Два таких простых выражения. «+» — это символ квантификатора службы. Он обеспечивает «жадный» режим, в котором все символы, соответствующие выражению, берутся до тех пор, пока не будет обнаружен путь, который ему не соответствует, или конец \ начало строки. Точка обозначает любой символ, поэтому берутся все символы до конца строки, которым предшествует цифра.
«\ D» d означает «цифры», в противном случае — цифры. Поскольку в приведенных выше примерах нет квантификатора после \ d, он есть. Если вы хотите исключить эту цифру из результатов, вы можете сделать это позже. В SEMTools есть простые способы удаления символов в начале или в конце ячейки.
Цифры могут быть выражены другими регулярными выражениями:
[0-9]
Как извлечь числа из строки? Регулярное выражение для этого было бы безумно простым:
\ D
В зависимости от режима извлечения результатом будет первая или все цифры в ячейке.
Если они должны отображаться не в непрерывной последовательности, а через разделитель, сохраняя фрагменты, в которых символы следовали друг за другом, выражение будет немного другим, с «жадным» квантификатором. А при извлечении вам нужно будет использовать разделитель.
\ d + [0-9]+
Это верно для любого другого символа, например со следующими числами:
Как вы можете видеть в приведенном выше примере, помимо чисел, представляющих годы, были извлечены и другие числа, например 1. Чтобы извлечь только 4-значные последовательности, вам нужно будет отредактировать выражение. Есть несколько вариантов:
\ d \ d \ d \ d [0-9] [0-9] [0-9] [0-9] \ d {4} [0-9] {4}
Последние два варианта включают квантификатор в фигурных скобках. Задает минимальное количество повторений символа или фрагмента строки, удовлетворяющего шаблону. Образец непосредственно перед квантификатором. В этом случае все 4 символа, являющиеся цифрами, должны находиться в одной строке.
Извлеките все 4-значные последовательности (год) с помощью разделителя
Выражение «[a-zA-Z]» относится ко всем латинским символам. Дефис как в этом, так и в предыдущем случае означает, что все символы между a и z и между A и Z взяты в общую таблицу символов Unicode. Квадратные скобки являются синонимом ИЛИ. Каждый из элементов или наборов внутри квадратных скобок проверяется, и выражение ничего не находит, если сравниваемая строка не содержит элементы внутри квадратных скобок.
Извлеките латинский алфавит с помощью регулярных выражений
Стандартные формулы RIGHT и LEFT позволяют извлечь последние и первые N символов из ячейки соответственно, но на этом их возможности заканчиваются.
Используя регулярные выражения, вы можете извлечь:
Если вам не нужно извлекать данные, а просто нужно проверить, соответствуют ли они шаблону, чтобы отфильтровать их позже, удобнее использовать процедуру, эквивалентную формуле REGEXMATCH.
В зависимости от того, является ли N необходимым или достаточным условием, требуются различные регулярные выражения. Другими словами, рассмотрите последовательности N + 1, N + 2 и т.д. В зависимости от обстоятельств. В таком случае выражение будет таким же, как указано выше:
\ d \ d \ d \ d [0-9] [0-9] [0-9] [0-9] \ d {4} [0-9] {4}
Если нас строго интересуют последовательности из N цифр, то задачу придется выполнять в 2 итерации:
Выражения для первой итерации будут соответственно:
(^ \ D) \ d \ d \ d \ d ($ \ D) (^ \ D) [0-9] [0-9] [0-9] [0-9] ($ \ D) (^ \ D) \ d {4} ($ \ D) (^ \ D) [0-9] {4} ($ \ D)
Если вы внимательно посмотрите на разницу в синтаксисе, вы сможете понять, что означают содержащиеся в ней символы:
Выражение для этой проверки будет:
= REGEXMATCH (A1; «^ \ d.*»)
В качестве альтернативы вы можете использовать процедуру проверки для копии исходного диапазона без необходимости вводить формулу. См. Примеры.
Найдите ячейки, начинающиеся с чисел, используя регулярное выражение
Наиболее частый случай такой замены — замена его пробелом, когда наша задача — просто удалить определенные символы из текста. Наиболее популярны:
Но бывают случаи, когда необходима настоящая замена, например, когда вам нужно заменить буквы на хвосты / умляуты / акценты и другие символы европейских алфавитов на их английские аналоги. Этот бизнес популярен среди SEO-специалистов, которые формируют URL-адреса сайтов из этих стран на основе исходной семантики. Это начало таблицы шаблонов для замены диакритических знаков латинскими символами с использованием RegEx при создании URL:
Диакритические знаки и их английские эквиваленты
Чтобы разделить символ ячейки на символ, просто извлеките все символы, разделенные разделителем. Выражением для извлечения будет обычная точка, она просто обозначает любой символ
Если строго придерживаться формулировки этой задачи, выполнить ее довольно сложно. Но с другой стороны, используя регулярные выражения, вы можете отделить числовые последовательности символов от нечисловых. Вот как будет выглядеть выражение:
([^ \ d.] + [\ d.]+)
А вот как этот процесс будет выглядеть на практике:
Разделите текст на числа и нечисловые символы (буквы и знаки препинания) с помощью регулярного выражения
При замене регулярным выражением в! SEMTools есть возможность заменить не все, а только первый найденный фрагмент, соответствующий шаблону. Это решает проблему вставки символов после первого слова. Мы просто заменяем первый пробел нужными нам символами, используя соответствующую процедуру:
вы также можете использовать функцию REPLACE для решения этой проблемы, но вы также можете использовать функцию подстановки регулярных выражений. В отличие от обычной процедуры замены, здесь можно заменить только первое вхождение. В этом случае первый пробел. Как видите, пространство ничем не отличается от обычного:
Замените первый пробел заменой регулярного выражения
Надстройка решает эту проблему в 2 клика с помощью готовой процедуры в меню «Изменить слова», но вы также можете использовать простое выражение для замены:
($) (^)
Выражения указывают, что пробелы или конец строки в первом случае и пробелы или начало строки во втором случае заменяются. Вертикальная черта — «ИЛИ».
И заменим, соответственно, пробел на символ слева или справа. Процедура добавит дополнительный пробел до или после ячейки, поэтому вы должны удалить его — «удалить лишние пробелы» или «Удалить символы в начале / конце ячейки«.
Вставка символа после каждого слова с использованием регулярного выражения
Когда дело доходит до извлечения определенных слов, регулярные выражения становятся невероятно сложными. Таким образом, надстройка! SEMTools позволяет легко применять шаблоны RegEx на уровне слов как отдельные объекты.
Вот как кажется извлекать слова, содержащие латинские буквы и цифры, из ряда слов с помощью регулярного выражения
([аз] \ д \ д [аз])
Обратите внимание, что это выражение означает, что цифра вместо буквы или буква вместо цифры должна следовать непосредственно, без промежуточных символов между ними. Если вам необходимо извлечь, помимо прочего, такие слова, как «asdf-13», «1234-d», вы должны указать возможность наличия символов между:
([az]. * \ d \ d. * [az])
