Regulární výrazy v excelu

Regulární výraz (zkracováno na regexp, regex či jen RE podle anglického regular expression) je řetězec popisující celou množinu řetězců, konkrétně regulární jazyk. Tolik Wikipedie. Nejčastější využití je vyhledávání textu nebo manipulace s textem. Pojďme se podívat na využití v Excelu.

Abychom mohli regulární výrazy v Excelu používat, nejdříve je nutné připojit knihovnu. To provedete následujícím způsobem.

Teď k vlastnímu příkladu. Použiji vzorová data z účetnictví ve třech sloupcích. V prvním sloupci jsou čísla účtů. V druhém jsou počáteční stavy a ve třetím jsou koncové stavy. Pro vytvoření kontrolních sestav (cashflow) bývají v účetních programech použity následující definice P21 + P22 + P25 - P252 - K321. Písmeno P zde představuje data ve sloupci počáteční stavy a písmeno K ve sloupci koncové stavy. Takže definice říká, sečti všechna data v počátečních stavech všech účtů začínajících 21 a k tomu přičti data počátečních stavů účtů začínajících 22 atd. Takových definic použiji v příkladu několik a vždy se liší. Definice samozřejmě lze editovat a tím měnit výsledný výpočet. Úkolem bude pomocí VBA a v něm použitého regulárního výrazu výpočet zprovoznit.

Na Internetu je mnoho informací o regulárních výrazech. Uvádím odkaz na dokumentaci od Microsoftu Regular Expression Language - Quick Reference, kde lze následně stáhnout v PDF. Zajímavý článek je na stránce stackoverflow.com. Pomocí regulárního výrazu ve VBA budu potřebovat najít všechny texty, kde je uveden znak představující sloupec s výpočtem (P nebo K) a za ním následující sekvenci čísel, které představují první čísla účtu. Následně pak v makru pomocí textové funkce dále zpracuji. Regulární výraz, který texty vyhledá, bude vypadat takto: (P|K)(\d+). Jak to funguje:

Kontrola regulárního výrazu na on-line testeru. Funguje jak má, najde všechny požadované řetězce.

Následně jsem připravil kód na test regulárního výrazu textu v buňce I4, kde je uvedeno P21 + P22 + P25 + P26 + P29 - P252 - P255 - P257.

Sub RegExpTest() Dim myMatch As Match Dim myMatches As MatchCollection Dim myRegExp As RegExp Dim myReplaceRegExp As RegExp Dim SubjectString As String Dim i As Integer Set myRegExp = New RegExp myRegExp.Global = True myRegExp.Pattern = "(P|K)(\d+)" Set myReplaceRegExp = New RegExp myReplaceRegExp.Global = True SubjectString = Range("I4") Set myMatches = myRegExp.Execute(SubjectString) Debug.Print "myMatches.Count: " & myMatches.Count i = 1 For Each myMatch In myMatches Debug.Print " " & i & ". Matches.Value: " & myMatch.Value Debug.Print " SubMatches: " & myMatch.SubMatches.Count & "(" & myMatch.SubMatches(0) & ";" & myMatch.SubMatches(1) & ")" i = i + 1 Next Set myRegExp = Nothing End Sub

Výpis testovací funkce. Všechny potřebné řetězce a subřetězce jsou nalezeny, takže tento základní kód lze aplikovat do výpočtu, který je zmíněn výše. Kompletní VBA řešení je v přiloženém souboru.

Uvedený příklad a další příklady si můžete stáhnout zde: Nejste přihlášen(a).