Разбор тэксту з дапамогай рэгулярных выразаў (RegExp) у Excel

Адной з самых працаёмкіх і непрыемных задач пры працы з тэкстам у Excel з'яўляецца разбор – разбор літарна-лічбавай «кашы» на складнікі і вылучэнне з яе патрэбных нам фрагментаў. Напрыклад:

• выманне паштовага індэкса з адраса (добра, калі паштовы індэкс заўсёды стаіць у пачатку, але што, калі гэта не так?)
• знаходжанне нумара і даты рахунку-фактуры з апісання плацяжу ў выпісцы з банка
• выманне ІНАЎ з пярэстых апісанняў кампаній у спісе контрагентаў
• пошук нумара аўтамабіля або нумара артыкула ў апісанні і інш.
  

Звычайна ў такіх выпадках пасля паўгадзіннага нуднага калупання ў тэксце ўручную пачынаюць прыходзіць у галаву думкі неяк аўтаматызаваць гэты працэс (асабліва калі дадзеных шмат). Ёсць некалькі рашэнняў з рознай ступенню складанасці і эфектыўнасці:

• Выкарыстоўваць убудаваныя тэкставыя функцыі Excel шукаць-выразаць-склеіць тэкст: ЛЕЎСІМВ (ЗЛЕВА), ПРАВА (СПРАВА), ПСТР (сярэдзіна), СЦЭПІТ (ЗБЯДЗЕЦЬ) і яго аналагі, Камбінат (СУМЕСНЫ ТЭКСТ), EXACT (ДАкладна) і г. д. Гэты спосаб добры, калі ў тэксце ёсць выразная логіка (напрыклад, індэкс заўсёды ў пачатку адраса). У адваротным выпадку формулы значна ўскладняюцца, а часам нават даходзяць да формул масіваў, што моцна тармозіць на вялікіх табліцах.
• Выкарыстанне як аператар падабенства тэксту з Visual Basic, загорнуты ў карыстальніцкую функцыю макраса. Гэта дазваляе рэалізаваць больш гнуткі пошук з выкарыстаннем сімвалаў падстаноўкі (*, #,? і г.д.). На жаль, гэты інструмент не можа вылучыць патрэбны падрадок з тэксту - праверце толькі, ці ён у ім утрымліваецца.

Акрамя вышэйпералічанага, ёсць яшчэ адзін падыход, вельмі добра вядомы ў вузкіх колах прафесійных праграмістаў, вэб-распрацоўшчыкаў і іншых тэхнароў - гэта regular expressions (Рэгулярныя выразы = RegExp = «рэгулярныя выразы» = «рэгулярныя выразы»). Прасцей кажучы, RegExp - гэта мова, у якой спецыяльныя сімвалы і правілы выкарыстоўваюцца для пошуку неабходных падрадкоў у тэксце, іх вылучэння або замены іншым тэкстам. Рэгулярныя выразы - гэта вельмі магутны і прыгожы інструмент, які на парадак пераўзыходзіць усе іншыя спосабы працы з тэкстам. Многія мовы праграмавання (C#, PHP, Perl, JavaScript…) і тэкставыя рэдактары (Word, Notepad++…) падтрымліваюць рэгулярныя выразы.

Microsoft Excel, на жаль, не мае стандартнай падтрымкі RegExp, але гэта можна лёгка выправіць з дапамогай VBA. Адкрыйце рэдактар ​​Visual Basic на ўкладцы распрацоўшчык (Распрацоўшчык) або спалучэнне клавіш Alt+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 = Шаблон 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, што патрэбны фрагмент (індэкс) павінен быць асобным словам, а не часткай іншага фрагмента (нумара тэлефона):

Тэлефон

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

ІТН

Тут усё крыху больш складана, таму што ІНАЎ (у нас) можа быць 10-значным (для юрыдычных асоб) і 12-значным (для фізічных асоб). Калі асабліва не прыдзірацца, то звычайным цалкам можна задаволіцца г{10,12}, але, строга кажучы, ён выцягне ўсе лічбы ад 10 да 12 сімвалаў, г.зн. і памылкова ўведзеныя 11 лічбаў. Правільней было б выкарыстоўваць два шаблоны, злучаныя лагічным аператарам АБО | (вертыкальная паласа):

Звярніце ўвагу, што ў запыце мы спачатку шукаем 12-бітныя лікі, а ўжо потым 10-бітныя. Калі мы напішам наш рэгулярны выраз наадварот, то для ўсіх, нават доўгіх 12-бітных TIN, будуць выдавацца толькі першыя 10 сімвалаў. Гэта значыць, пасля спрацоўвання першай умовы далейшая праверка больш не праводзіцца:

Гэта прынцыповае адрозненне аператара | са стандартнай лагічнай функцыі Excel OR (OR), дзе перастаноўка аргументаў не змяняе вынік.

Арт

У многіх кампаніях таварам і паслугам прысвойваюцца унікальныя ідэнтыфікатары - артыкулы, SAP-коды, SKU і г. д. Калі ў іх абазначэнні ёсць логіка, то іх можна лёгка вылучыць з любога тэксту з дапамогай рэгулярных выразаў. Напрыклад, калі мы ведаем, што нашы артыкулы заўсёды складаюцца з трох вялікіх ангельскіх літар, злучка і наступнага трохзначнага нумара, то:

Логіка шаблону простая. [А-Я] – любыя вялікія літары лацінскага алфавіту. Наступны квантыфікатар 3 {} кажа, што для нас важна, каб такіх літар было роўна тры. Пасля злучка чакаем тры лічбы, таму дадаем у канцы г{3}

Грашовыя сумы

Аналагічным спосабам, што і ў папярэднім абзацы, з апісання тавару можна таксама выцягнуць цэны (кошты, ПДВ…). Калі праз злучок пазначаюцца, напрыклад, грашовыя сумы, то:

шаблон d з квантарам + шукае любую лічбу да злучка, і г{2} будзе шукаць капейкі (дзве лічбы) пасля.

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

Аўтамабільныя нумары

Калі не браць спецтэхніку, прычэпы і іншыя матацыклы, то стандартны аўтамабільны нумар разбіраецца па прынцыпе «літара - тры лічбы - дзве літары - код рэгіёна». Прычым код рэгіёна можа быць 2-х або 3-значным, а ў якасці літар выкарыстоўваюцца толькі тыя, якія па вонкавым выглядзе падобныя на лацінскі алфавіт. Такім чынам, наступны рэгулярны выраз дапаможа нам атрымаць лічбы з тэксту:

час

Для вылучэння часу ў фармаце HH:MM падыдзе наступны рэгулярны выраз:

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

• [0-1]г – любы лік у дыяпазоне 00-19
• 2[0-3] – любы лік у дыяпазоне 20-23

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

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

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

Праверку можна арганізаваць з дапамогай наступнага простага рэгулярнага выразу:

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

Горад з адраса

Дапусцім, нам трэба выцягнуць горад з адраснага радка. Дапаможа звычайная праграма, якая вылучыць тэкст з «г». да наступнай коскі:

Давайце больш падрабязна разгледзім гэты ўзор.

Калі вы прачыталі тэкст вышэй, то вы ўжо зразумелі, што некаторыя сімвалы ў рэгулярных выразах (кропкі, зорачкі, знакі даляра і г.д.) маюць асаблівае значэнне. Калі вам трэба шукаць самі гэтыя сімвалы, перад імі ставіцца зваротная косая рыса (часам яе называюць экранаванне). Таму пры пошуку фрагмента “г.” мы павінны пісаць у рэгулярным выразе Спадар калі мы шукаем плюс, то + і г.д.

Наступныя два сімвалы ў нашым шаблоне, кропка і квантарная зорачка, абазначаюць любую колькасць любых знакаў, г.зн. любую назву горада.

У канцы шаблона стаіць коска, таму што мы шукаем тэкст з «g». да коскі. Але косак у тэксце можа быць некалькі, праўда? Не толькі за горадам, але і за вуліцай, дамамі і г. д. На якім з іх спыніцца наш запыт? Для гэтага пытальнік. Без яго наш рэгулярны выраз атрымаў бы самы доўгі радок:

З пункту гледжання рэгулярных выразаў, такі шаблон з'яўляецца «прагным». Каб выправіць сытуацыю, патрэбен пытальнік – ён робіць квантыфікатар, пасьля якога стаіць “скупы”, – а наш запыт бярэ тэкст толькі да першай сустрэчнай коскі пасьля “g.”:

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

Яшчэ адна вельмі распаўсюджаная сітуацыя - выманне імя файла з поўнага шляху. Тут дапаможа просты рэгулярны выраз формы:

Фішка тут у тым, што пошук, па сутнасці, адбываецца ў адваротным кірунку – з канца ў пачатак, таму што ў канцы нашага шаблона знаходзіцца $, і мы шукаем усё перад ім да першай зваротнай касой рысы справа. Зваротная касая рыса экрануецца, як і кропка ў папярэднім прыкладзе.

PS

«Бліжэй да канца» я хачу ўдакладніць, што ўсё вышэйсказанае - малая частка ўсіх магчымасцяў, якія даюць рэгулярныя выразы. Спецыяльных знакаў і правілаў іх выкарыстання вельмі шмат, на гэтую тэму напісаны цэлыя кнігі (для пачатку раю хаця б гэтую). У пэўным сэнсе напісанне рэгулярных выразаў - амаль мастацтва. Амаль заўсёды прыдуманы рэгулярны выраз можна палепшыць або дапоўніць, зрабіўшы яго больш элегантным або здольным працаваць з больш шырокім спектрам ўваходных даных.

Каб прааналізаваць і разабраць рэгулярныя выразы іншых людзей або адладзіць свае ўласныя, існуе некалькі зручных онлайн-сэрвісаў: RegEx101, RegExr і больш

На жаль, не ўсе магчымасці класічных рэгулярных выразаў падтрымліваюцца ў VBA (напрыклад, зваротны пошук або класы POSIX) і могуць працаваць з кірыліцай, але я думаю, што таго, што ёсць, дастаткова на першы раз, каб парадаваць вас.

Калі вы не пачатковец у гэтай тэме і вам ёсць чым падзяліцца, пакіньце ў каментарах рэгулярныя выразы, карысныя пры працы ў Excel. Адзін розум — добра, а два боты — пара!

• Замена і ачыстка тэксту з дапамогай функцыі SUBSTITUTE
• Пошук і вылучэнне лацінскіх літар у тэксце
• Пошук бліжэйшага падобнага тэксту (Іваноў = Ивонов = Иванов і г.д.)