RegEx avagy keresés felturbózva

RegEx vagy RegExp:

A “Regular Expression” (reguláris kifejezés) rövidítése. Szövegben történő keresést tesz lehetővé, mégpedig rugalmas keresési kulcsszavak megadásával. Mitől rugalmas a keresési kulcsszó? Hát attól, hogy itt nem konkrét szöveget vagy szövegrészt keresek, hanem egy keresési mintát adok meg.

Példa: az alábbi Megnevezés oszlop soraiból akarom kinyerni a helyrajzi számokat vagyis a megjelölt részeket kiíratni a “Hrsz” oszlopba:

Akit csak az eredmény és a mintakódok érdekelnek, az kattintson ide.

Elsőre talán nem is annyira nyilvánvaló a feladat összetettsége. Ugyanis a szövegben található számok

– eltérő hosszúságúak

– némelyike 0-val kezdődik, amit ha megkapunk majd számként kezelünk, akkor az Excel levágja: pl. a 089-ből 89 lesz

– néhol a kifejezés végén, máshol a közepén vannak

– némelyike „/” jelet is tartalmaz

– valahol teljesen hiányoznak

– mellett a „/” jeleket is meg kellene tartani

Egyértelmű, hogy ezt Excel képlet(ek)tel nem tudom egykönnyen kinyerni. Ezért inkább a fentebb említett keresési mintát adom meg, melynek lényege:

keresd meg a szövegen belül az összes számot és „/” jelet és az eredményt írd ki a megfelelő cellába. Vagyis pl. ebből…

Vidéki családi ház 9956/1 hrsz

…ezt adja vissza:

9956/1

Ebben a posztban a fenti probléma megoldásán keresztül mutatom be, miért annyira sokoldalú és hatékony a VBA RegEx használata.

Ismertetés:

A RegEx számos egyéb programozási nyelvben elérhető (Java, Python stb). Ettől eltér a VBA-ban lévő RegEx (VBA RegEx) vagyis az itt látott kód az Excel/VBA berkein belül fog működni de hát nekünk ez remélhetőleg elegendő is 😊

Alkalmazására példák:

– Szöveg keresése és a találat kinyerése keresési minta alapján (ezt valósítjuk meg lentebb)

– keresési minta alapján megtalált szöveg(rész) kicserélése másik szöveg(rész)re

– Bemeneti vagy kimeneti szöveg validálása – feltéve persze, hogy van egy specifikus minta. Pl. egy adott fájl neve dátumot tartalmazzon ebben a formátumban: 20190915). Erre is lesz lentebb példa.

A RegEx karakterek sorozatából áll, így adom meg a keresési mintát. Ez a minta az alábbi összetevőket tartalmazhatja:

– literális (szó vagy betű szerinti) karakterek, pl. a c jelentése: csak a c-re, mint szövegre keresek rá

– metakarakterek: egy karakter vagy több karakter kombinációja melynek egyedi jelentése van. Pl. a c.-nál a pont is egy ilyen karakter. Néhány fontosabb metakarakter:

. Az egyszerű pont egy joker-karakter. Bármely karakterrel egyezhet. Vagyis a c. utasítás egyezést fog mutatni nem csak a c-re, hanem pl. ezekre is: c0, cx stb.

[ ] A szögletes zárójeleken belüli kifejezés jelentése: bármelyik adott karakterrel való egyezés. Pl. [abc] egyezést ad a-ra vagy b-re vagy c-re. Másik példa: [0-9] egyezést ad az összes számra 0-tól 9-ig

[^ ] Olyan karakterrel történő egyezés, mely nincs benne a zárójelben. Pl. [^abc] minden karakterre egyezést ad, kivéve az a-t, b-t vagy a c-t

– A kötőjel intervallum megadására szolgál. Pl. [a-z] az összes angol nyelvű kisbetűt adja vissza, míg az előbb említett [0-9] az összes számot

{} A kapcsos zárójelen belüli szám jelentése: az előző parancsnak ennyiszer kell ismétlődni. Pl. ha a „-” nek 1x kell ismétlődnie: [-]{1}

Használata:

A RegEx beépített funkció, csak hozzá kell adnunk a VBA-ban a megfelelő referenciát a VBScript.RegExp-hez:

Excel-ben ALT+F11-gyel megnyitjuk a VBA szerkesztőjét, majd:

Tools -> References… -> tegyük be a pipát a „Microsoft VBScript Regular Expressions 5.5” elé:

Ezt azonban csak akkor kell megtennünk, ha ún. korai kötést (early binding) akarunk alkalmazni. Én most a késői kötést (late binding) fogom használni, kompatibilitási okokból + itt nem kell a fenti referenciát hozzáadnunk. A korai és késői kötés lényegét és a különbségeket ebben a (videót is tartalmazó) posztban ismertettem.

Térjünk vissza a problémára, ahol a helyrajzi számokat kell kinyerni. Erre egy makró képletet írtam, mely használat közben így néz ki:

Az adott munkafüzetben nyissuk meg a Visual Basic szerkesztőjét (ALT + F11-gyel). Majd szúrjunk be egy modult és az alábbi kódot adjuk hozzá:

Function Hrsz_Kinyerese(Megnevezes As String)

Dim objRegEx As Object, allMatches As Object, Item, Kimenet

'a számokat adja vissza és a "/" karaktert, pl.: "Vidéki családi ház 9956/1 hrsz"-bõl: "9956/1"
'késõi kötés (late binding)
'Készítette: XLMotyo (https://xlmotyo.hu)

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx
    .Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést
    .IgnoreCase = True
    .Pattern = "[0-9]|[/]" 'számok vagy a "/" karakter
    .MultiLine = True
End With

'egyezõ karakterek eltárolása egy tömbben
Set allMatches = objRegEx.Execute(Megnevezes)

'tömb elemeinek (vagyis az egyezõ karakterek) összefûzése
For Each Item In allMatches
    Kimenet = Kimenet & Item.Value
Next Item

If IsEmpty(Kimenet) Then
    Hrsz_Kinyerese = ""
Else
    Hrsz_Kinyerese = Kimenet 'összefûzött karakterek kiíratása
End If

End Function

Function Hrsz_Kinyerese(Megnevezes As String)

Dim objRegEx As Object, allMatches As Object, Item, Kimenet

'a számokat adja vissza és a "/" karaktert, pl.: "Vidéki családi ház 9956/1 hrsz"-bõl: "9956/1"

'késõi kötés (late binding)

'Készítette: XLMotyo (https://xlmotyo.hu)

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx

.Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést

.IgnoreCase = True

.Pattern = "[0-9]|[/]" 'számok vagy a "/" karakter

.MultiLine = True

End With

'egyezõ karakterek eltárolása egy tömbben

Set allMatches = objRegEx.Execute(Megnevezes)

'tömb elemeinek (vagyis az egyezõ karakterek) összefûzése

For Each Item In allMatches

Kimenet = Kimenet & Item.Value

Next Item

If IsEmpty(Kimenet) Then

Hrsz_Kinyerese = ""

Else

Hrsz_Kinyerese = Kimenet 'összefûzött karakterek kiíratása

End If

End Function

A kódban megjegyzésként hozzáadtam a megértést segítő magyarázatokat. A fenti makró formula csak az azt tartalmazó munkafüzetben használható.

További gyakorlati példák RegEx-re:

1. Email cím validálás: ellenőrizni tudjuk, hogy a beírt email formátuma korrekt-e: a makrós képlet nevét (EmailCimValidalas) begépeljük egy cellába, ahogy a fenti animáción:

Function EmailCimValidalas(Megnevezes As String) As Boolean

Dim objRegEx As Object

'késõi kötés (late binding)
'Készítette: XLMotyo (https://xlmotyo.hu)
'inspiráció: https://social.msdn.microsoft.com/Forums/en-US/eaa099b6-3bfe-45f0-b391-c1816e823261/ _
vba-function-for-checking-email-syntax-validity?forum=accessdev

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx
    .Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést
    .IgnoreCase = True
    .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
    .MultiLine = True
End With

objRegEx.Test (Megnevezes)

EmailCimValidalas = objRegEx.Test(Megnevezes)

End Function

Function EmailCimValidalas(Megnevezes As String) As Boolean

Dim objRegEx As Object

'késõi kötés (late binding)

'Készítette: XLMotyo (https://xlmotyo.hu)

'inspiráció: https://social.msdn.microsoft.com/Forums/en-US/eaa099b6-3bfe-45f0-b391-c1816e823261/ _

vba-function-for-checking-email-syntax-validity?forum=accessdev

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx

.Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést

.IgnoreCase = True

.Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"

.MultiLine = True

End With

objRegEx.Test (Megnevezes)

EmailCimValidalas = objRegEx.Test(Megnevezes)

End Function

2. Aktív munkafüzet nevében előfordul-e egy adott dátum formátum: pl.: 20180921. A dátum álljon egy számsorból (év, hónap és nap sorrendben) és az év legyen >=2010 és <=2019: ezt a kódot csak futtatni kell, nem cellába beírni a nevét, mint a többi makrónál:

Sub Datumformatum_FajlNeveben()

Dim objRegEx As Object, Elem, S As String, Sz As Long

'késõi kötés (late binding)
'Készítette: XLMotyo (https://xlmotyo.hu)

Set objRegEx = CreateObject("vbscript.regexp")

S = ActiveWorkbook.Name

objRegEx.Pattern = "(201[0-9]{1}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01]))" 'dátum minta, pl.: 20180921 +
'az év >=2010 és <=2019; hónap: 1 és 12 között; nap: 1 és 31 között. Hátránya, hogy elfogad fals dátumot is: 20180931-et.

objRegEx.Global = True

Sz = -1
For Each Elem In objRegEx.Execute(S)
    Sz = Sz + 1
    Exit For
Next Elem

If Sz = -1 Then
    MsgBox "A megfelelõ dátum mintát (pl. '20180921') NEM tartalmazza a fájl neve! " & _
    "Nevezd át a fájlt a formátumnak megfelelõen!", , "Aktív munkafüzet neve: " & ActiveWorkbook.Name
Else
    MsgBox "A megfelelõ dátum mintát (itt: " & Elem.Value & ") tartalmazza a fájl neve! " & _
    "Remek munka!", , "Aktív munkafüzet neve: " & ActiveWorkbook.Name
End If

End Sub

Sub Datumformatum_FajlNeveben()

Dim objRegEx As Object, Elem, S As String, Sz As Long

'késõi kötés (late binding)

'Készítette: XLMotyo (https://xlmotyo.hu)

Set objRegEx = CreateObject("vbscript.regexp")

S = ActiveWorkbook.Name

objRegEx.Pattern = "(201[0-9]{1}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01]))" 'dátum minta, pl.: 20180921 +

'az év >=2010 és <=2019; hónap: 1 és 12 között; nap: 1 és 31 között. Hátránya, hogy elfogad fals dátumot is: 20180931-et.

objRegEx.Global = True

Sz = -1

For Each Elem In objRegEx.Execute(S)

Sz = Sz + 1

Exit For

Next Elem

If Sz = -1 Then

MsgBox "A megfelelõ dátum mintát (pl. '20180921') NEM tartalmazza a fájl neve! " & _

"Nevezd át a fájlt a formátumnak megfelelõen!", , "Aktív munkafüzet neve: " & ActiveWorkbook.Name

Else

MsgBox "A megfelelõ dátum mintát (itt: " & Elem.Value & ") tartalmazza a fájl neve! " & _

"Remek munka!", , "Aktív munkafüzet neve: " & ActiveWorkbook.Name

End If

End Sub

3. Magyar telefonszám validálás adott formátumban, mely az országkóddal (+36) kezdődik, pl.: +3630-123-45-67. A makrós képlet nevét (MagyarTelefonszamValidalas) begépeljük egy cellába, ahogy a fenti animáción:

Function MagyarTelefonszamValidalas(Megnevezes As String) As Boolean

Dim objRegEx As Object

'késõi kötés (late binding)
'Készítette: XLMotyo (https://xlmotyo.hu)
'magyar telefonszámok validálása: +36-tal kezdõdjön és legyen 20-as/30-as/70-es. Pl.: +3630-123-45-67

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx
    .Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést
    .IgnoreCase = True
    .Pattern = "([+]{1}36[237]{1}(0){1}[-]{1}[0-9]{3}[-]{1}[0-9]{2}[-]{1}[0-9]{2})"
    .MultiLine = True
End With

objRegEx.Test (Megnevezes)

MagyarTelefonszamValidalas = objRegEx.Test(Megnevezes)

End Function

Function MagyarTelefonszamValidalas(Megnevezes As String) As Boolean

Dim objRegEx As Object

'késõi kötés (late binding)

'Készítette: XLMotyo (https://xlmotyo.hu)

'magyar telefonszámok validálása: +36-tal kezdõdjön és legyen 20-as/30-as/70-es. Pl.: +3630-123-45-67

Set objRegEx = CreateObject("vbscript.regexp") 'RegExp objektum létrehozása

With objRegEx

.Global = True 'True: minden egyezést visszaad; False: csak az elsõ egyezést

.IgnoreCase = True

.Pattern = "([+]{1}36[237]{1}(0){1}[-]{1}[0-9]{3}[-]{1}[0-9]{2}[-]{1}[0-9]{2})"

.MultiLine = True

End With

objRegEx.Test (Megnevezes)

MagyarTelefonszamValidalas = objRegEx.Test(Megnevezes)

End Function

A RegEx-ről külön könyvek is fellelhetők, vagyis a téma jóval kiterjedtebb, mint az itt bemutatott magyarázat. A fenti ismertetőből és példákból láthattuk, mennyire hatékony. Azonban ne feledjük: számos esetben az Excel/VBA beépített funkciói elegendőek, hogy megkapjuk a kívánt eredményt (mint a Keresés és Csere).

Felhasznált források (angol):

http://learnexcelmacro.com/wp/2018/09/regex-tester-in-excel-vba/

https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/

https://en.wikipedia.org/wiki/Regular_expression

Remélem hasznos volt a poszt.
Kérdésed, észrevételed van? Hívj vagy dobj egy emailt az xlmotyo@gmail.com-ra

RegEx vagy RegExp:

Akit csak az eredmény és a mintakódok érdekelnek, az kattintson ide.

Elsőre talán nem is annyira nyilvánvaló a feladat összetettsége. Ugyanis a szövegben található számok

Ismertetés:

Alkalmazására példák:

Használata:

További gyakorlati példák RegEx-re:

1. Email cím validálás: ellenőrizni tudjuk, hogy a beírt email formátuma korrekt-e: a makrós képlet nevét (EmailCimValidalas) begépeljük egy cellába, ahogy a fenti animáción:

2. Aktív munkafüzet nevében előfordul-e egy adott dátum formátum: pl.: 20180921. A dátum álljon egy számsorból (év, hónap és nap sorrendben) és az év legyen >=2010 és <=2019: ezt a kódot csak futtatni kell, nem cellába beírni a nevét, mint a többi makrónál:

3. Magyar telefonszám validálás adott formátumban, mely az országkóddal (+36) kezdődik, pl.: +3630-123-45-67. A makrós képlet nevét (MagyarTelefonszamValidalas) begépeljük egy cellába, ahogy a fenti animáción:

Ajánló