Синтаксичний аналіз тексту за допомогою регулярних виразів (RegExp) у Excel

Одне з найбільш трудомістких і неприємних завдань під час роботи з текстом у Excel синтаксичний аналіз – розібрати буквено-цифрову «кашу» на складові та витягти з неї потрібні нам фрагменти. Наприклад:

• вилучення поштового індексу з адреси (це добре, якщо поштовий індекс завжди стоїть на початку, але що, якщо це не так?)
• знаходження номера та дати рахунку-фактури з опису платежу у виписці з банку
• витяг ІПН із строкатих описів компаній у списку контрагентів
• пошук номера автомобіля або артикула в описі тощо.
  

Зазвичай в таких випадках після півгодини нудного колупання в тексті вручну починають приходити в голову думки якось автоматизувати цей процес (особливо, якщо даних багато). Є кілька рішень з різним ступенем складності-ефективності:

• Скористайтесь вбудовані текстові функції Excel для пошуку-вирізання-склеювання тексту: ЛЕВСІМВ (ВЛІВО), ПРАВО (ПРАВО), PSTR (середина), СЦЕПІТ (З'ЄДНАТИ) та його аналоги, СПІЛЬНО (СПІЛЬНИЙ ТЕКСТ), EXACT (ТОЧНО) і т. д. Цей спосіб хороший, якщо в тексті є чітка логіка (наприклад, індекс завжди стоїть на початку адреси). В іншому випадку формули значно ускладнюються, а іноді навіть доходять до формул масивів, що сильно сповільнює роботу на великих таблицях.
• використання як оператор подібності тексту з Visual Basic, загорнений у спеціальну функцію макросу. Це дає змогу реалізувати більш гнучкий пошук за допомогою символів узагальнення (*, #,? тощо). На жаль, цей інструмент не може витягти потрібний підрядок із тексту – перевірте лише, чи він міститься в ньому.

Крім перерахованого вище, існує ще один підхід, дуже добре відомий у вузьких колах професійних програмістів, веб-розробників та інших технарів – це регулярні вирази (Регулярні вирази = RegExp = “regexps” = “regulars”). Простіше кажучи, RegExp — це мова, у якій спеціальні символи та правила використовуються для пошуку потрібних підрядків у тексті, їх вилучення або заміни іншим текстом. Регулярні вирази - це дуже потужний і красивий інструмент, який на порядок перевершує всі інші способи роботи з текстом. Багато мов програмування (C#, PHP, Perl, JavaScript…) і текстові редактори (Word, Notepad++…) підтримують регулярні вирази.

Microsoft Excel, на жаль, не має стандартної підтримки RegExp, але це можна легко виправити за допомогою VBA. Відкрийте редактор Visual Basic на вкладці розробник (розробник) або комбінацію клавіш інший+F11. Потім вставте новий модуль через меню Вставка – модуль і скопіюйте туди текст такої функції макросу:

Загальнодоступна функція RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Текст) Потім установіть matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Вихід із функції End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Тепер ми можемо закрити редактор Visual Basic і повернутися до Excel, щоб випробувати нашу нову функцію. Його синтаксис такий:

=RegExpExtract( Txt; шаблон; елемент)

де

• TXT – комірка з текстом, який ми перевіряємо і з якого хочемо витягти потрібний нам підрядок
• модель – маска (шаблон) для пошуку підрядка
• пункт – порядковий номер підрядка, який виділяється, якщо їх кілька (якщо не вказано, то відображається перше входження)

Найцікавішим тут, звичайно, є Pattern – шаблонний рядок спеціальних символів «на мові» RegExp, який вказує, що саме і де ми хочемо знайти. Ось основні з них, щоб почати:

Якщо ми шукаємо певну кількість символів, наприклад, шестизначний поштовий індекс або всі трибуквені коди товару, то ми приходимо на допомогу кількісні показники or кількісні показники це спеціальні вирази, які вказують кількість символів для пошуку. Квантори застосовуються до символу, який стоїть перед ним:

Тепер переходимо до найцікавішого – розбору застосування створеної функції та того, що ми дізналися про патерни на практичних прикладах з життя.

Виділення чисел із тексту

Для початку розберемо простий випадок – з буквено-цифрової каші потрібно витягти перше число, наприклад, потужність джерел безперебійного живлення з прайс-листа:

Логіка регулярного виразу проста: d означає будь-яку цифру, а квантор + говорить, що їх кількість повинна бути один або більше. Подвійний мінус перед функцією потрібен, щоб «на льоту» перетворити витягнуті символи в повне число з числа як тексту.

Поштовий індекс

На перший погляд тут все просто – шукаємо рівно шість цифр поспіль. Ми використовуємо спеціальний символ d для цифри та квантора 6 {} за кількістю символів:

Однак можлива ситуація, коли ліворуч від індексу в рядку стоїть ще один великий набір цифр (номер телефону, ІПН, банківський рахунок і т. д.). Тоді наш регулярний сезон витягне перші 6 цифри з нього, тобто не працюватиме коректно:

Щоб цього не сталося, нам потрібно додати модифікатор по краях нашого регулярного виразу b що позначає кінець слова. Це дасть зрозуміти Excel, що потрібний фрагмент (індекс) має бути окремим словом, а не частиною іншого фрагмента (номера телефону):

Телефони

Проблема пошуку номера телефону в тексті полягає в тому, що існує дуже багато варіантів написання цифр – з дефісом і без, через пробіли, з кодом регіону в дужках і без нього тощо. Тому, на мою думку, простіше спочатку очистіть усі ці символи з вихідного тексту за допомогою кількох вкладених функцій ЗАМІННИК (ЗАМІНА)так, щоб він склеївся в єдине ціле, а потім з примітивним регулярним d{11} витягніть 11 цифр поспіль:

СОІ

Тут трохи складніше, тому що ІПН (у нас) може бути 10-значним (для юридичних осіб) або 12-значним (для фізичних осіб). Якщо особливо не причепитися, то цілком можна задовольнитися регулярним d{10,12}, але, власне кажучи, витягне всі числа від 10 до 12 символів, тобто і помилково введені 11 цифр. Правильніше було б використовувати два шаблони, з'єднані логічним оператором АБО | (вертикальна смуга):

Зверніть увагу, що в запиті ми спочатку шукаємо 12-розрядні числа, а вже потім 10-розрядні. Якщо ми напишемо наш регулярний вираз навпаки, тоді він витягне для всіх, навіть довгих 12-бітних TIN, лише перші 10 символів. Тобто після спрацьовування першої умови подальша перевірка більше не виконується:

У цьому принципова відмінність оператора | зі стандартної логічної функції Excel OR (АБО), де перегрупування аргументів не змінює результат.

Артикули товару

У багатьох компаніях унікальні ідентифікатори присвоюються товарам і послугам – артикулам, SAP-кодам, SKU і т. д. Якщо в їх нотації є логіка, то їх можна легко витягнути з будь-якого тексту за допомогою регулярних виразів. Наприклад, якщо ми знаємо, що наші статті завжди складаються з трьох великих англійських літер, дефіса та подальшого тризначного числа, тоді:

Логіка шаблону проста. [AZ] – означає будь-які великі літери латинського алфавіту. Наступний квантор 3 {} каже, що для нас важливо, щоб таких букв було рівно три. Після дефісу чекаємо три цифри, тому додаємо в кінці d{3}

Суми готівки

Подібно до попереднього абзацу ви також можете витягти ціни (вартість, ПДВ…) з опису товару. Якщо грошові суми, наприклад, позначаються через дефіс, то:

Викрійки d з квантором + шукає будь-яке число до дефіса та d{2} буде шукати пенні (дві цифри) після.

Якщо вам потрібно витягти не ціни, а ПДВ, то ви можете використовувати третій необов'язковий аргумент нашої функції RegExpExtract, який задає порядковий номер елемента, який витягується. І, звичайно, можна замінити функцію ЗАМІННИК (ЗАМІНА) у результатах ставте дефіс через стандартний десятковий роздільник і додайте подвійний мінус на початку, щоб Excel інтерпретував знайдений ПДВ як звичайне число:

Автомобільні номерні знаки

Якщо не брати спецтехніку, причепи та інші мотоцикли, то стандартний номер автомобіля розбирається за принципом «літера – три цифри – дві літери – код регіону». Причому код регіону може бути 2-х або 3-значним, а в якості літер використовуються тільки ті, які зовні схожі на латинський алфавіт. Таким чином, наступний регулярний вираз допоможе нам отримати числа з тексту:

Time

Щоб отримати час у форматі ГГ:ХХ, підходить такий регулярний вираз:

Після фрагмента товстої кишки [0-5]d, як легко зрозуміти, задає будь-яке число в діапазоні 00-59. Перед двокрапкою в круглих дужках працюють два шаблони, розділені логічним АБО (реквізит):

• [0-1]d – будь-яке число в діапазоні 00-19
• 2[0-3] – будь-яке число в діапазоні 20-23

До отриманого результату можна додатково застосувати стандартну функцію Excel ЧАС (КОМАНДА)щоб перевести його в формат часу, зрозумілий програмі і придатний для подальших розрахунків.

Перевірка пароля

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

Перевірку можна організувати за допомогою наступного простого регулярного виразу:

Фактично, з таким шаблоном ми вимагаємо, щоб між початком (^) і кінець ($) в нашому тексті були тільки символи з набору, поданого в квадратних дужках. Якщо вам також потрібно перевірити довжину пароля (наприклад, не менше 6 символів), то квантор + у формі можна замінити інтервалом «шість і більше». {6,}:

Місто з адреси

Припустимо, нам потрібно витягнути місто з адресного рядка. Допоможе звичайна програма, витягнувши текст з «г». до наступної коми:

Розглянемо цей візерунок ближче.

Якщо ви прочитали текст вище, то ви вже зрозуміли, що деякі символи регулярних виразів (крапки, зірочки, знаки долара тощо) мають особливе значення. Якщо вам потрібно шукати ці символи, перед ними ставиться зворотна коса риска (іноді називається екранування). Тому при пошуку фрагмента “g.” ми повинні писати в регулярному виразі Пан якщо ми шукаємо плюс, то + і т.п.

Наступні два символи в нашому шаблоні, крапка та зірочка квантифікатора, позначають будь-яку кількість будь-яких символів, тобто назву будь-якого міста.

У кінці шаблону стоїть кома, тому що ми шукаємо текст із «g». до коми. Але в тексті може бути кілька ком, чи не так? Не тільки за містом, а й за вулицею, будинком і т. д. На якому з них зупиниться наш запит? Ось для чого знак питання. Без нього наш регулярний вираз витягне найдовший із можливих рядків:

З точки зору регулярних виразів, такий шаблон є «жадібним». Щоб виправити ситуацію, потрібен знак питання – він робить квантифікатор, після якого стоїть «скупим» – і наш запит бере текст лише до першої зустрічної коми після «g.»:

Ім'я файлу з повного шляху

Іншою дуже поширеною ситуацією є вилучення імені файлу з повного шляху. Тут допоможе простий регулярний вираз форми:

Фішка тут у тому, що пошук насправді відбувається у зворотному напрямку – від кінця до початку, тому що в кінці нашого шаблону знаходиться $, і ми шукаємо все перед ним до першої зворотної скісної риски справа. Зворотний слеш екранується, як і крапка в попередньому прикладі.

PS

«Під кінець» я хочу пояснити, що все вищесказане є невеликою частиною всіх можливостей, які надають регулярні вирази. Спецсимволів і правил їх використання дуже багато, на цю тему написані цілі книги (для початку рекомендую хоча б цю). У певному сенсі написання регулярних виразів – це майже мистецтво. Майже завжди винайдений регулярний вираз можна вдосконалити або доповнити, зробивши його більш елегантним або здатним працювати з ширшим діапазоном вхідних даних.

Для аналізу та аналізу чужих регулярних виразів або налагодження власних є кілька зручних онлайн-сервісів: RegEx101, RegExr і більше

На жаль, не всі функції класичних регулярних виразів підтримуються у VBA (наприклад, зворотний пошук або класи POSIX) і можуть працювати з кирилицею, але я думаю, що того, що є, достатньо для першого разу, щоб вас порадувати.

Якщо ви не новачок у цій темі, і вам є чим поділитися, залиште регулярні вирази, корисні під час роботи в Excel, у коментарях нижче. Один розум добре, але два чоботи пара!

• Заміна та очищення тексту за допомогою функції ЗАМІНА
• Пошук і виділення латинських літер у тексті
• Пошук найближчого подібного тексту (Іванов = Івонов = Іваноф тощо)