Kā programmā Excel var izmantot regulārās izteiksmes un izmantot jaudīgās režģa formas, kas līdzīgas Excel režģim, lai manipulētu ar datiem?
Es saprotu, ka Regex nav ideāli piemērots daudzām situācijām (Lietot vai nelietot regulārās izteiksmes?), jo Excel var izmantot Left
, Mid
, Right
, Instr
tipa komandas līdzīgām manipulācijām.
Regulārās izteiksmes tiek izmantotas rakstu saskaņošanai.
Lai izmantotu programmā Excel, izpildiet šādas darbības :
1. solis: Pievienojiet VBA atsauci uz "Microsoft VBScript Regular Expressions 5.5"
2. solis: Definējiet savu modeli
Bāzes definīcijas:
-
Diapazons.
a-z
atbilst mazajiem burtiem no a līdz z.0-5
atbilst jebkuram skaitlim no 0 līdz 5[]
Atbilst tieši vienam no šajos iekavās esošajiem objektiem.
[a]
atbilst burtam a[abc]
atbilst vienam burtam, kas var būt a, b vai c.[a-z]
atbilst jebkuram mazajam alfabēta burtam.()
Atgriešanas nolūkos grupē dažādas sakritības. Sk. turpmāk sniegtos piemērus.
{}
Korekcijas reizinātājs pirms tā definētā parauga atkārtotām kopijām.
[a]{2}
atbilst diviem secīgiem mazajiem burtiem a: aa
[a]{1,3}`` atbilst vismaz vienam un ne vairāk kā trim mazajiem burtiem
a,
aa,
aaa`+
Atbilst vismaz vienam vai vairākiem pirms tā definētajiem paraugiem.
a+
atbilst secīgiem a's a
, aa
, aaa
un tā tālāk.?
Atbilst nullei vai vienam no pirms tā definētajiem paraugiem.
[a-z]?
atbilst tukšai virknei vai jebkuram mazajam burtam.*
Atbilst nullei vai vairākiem pirms tā definētajiem paraugiem.
[a-z]*
atbilst tukšai virknei vai mazo burtu virknei..
Atbilst jebkurai rakstzīmei, izņemot jauno rindu \n
.
a.
atbilst divu rakstzīmju virknei, kas sākas ar a un beidzas ar jebko, izņemot \n
.|
OR operators
a|b
nozīmē, ka var salīdzināt vai nu a
, vai b
.sarkana|balta|oranža
atbilst tieši vienai no krāsām.^
NOT operators
[^0-9]
rakstzīme nevar saturēt skaitli.[^aA]
rakstzīme nevar būt mazā a
vai lielā A
.``` Izvairās no sekojošās īpašās rakstzīmes (aizstāj iepriekš minēto)
\.
, \\
, \(
, \?
, \$
, \^
Anchoring Patterns:
^
Saskaņošanai jānotiek virknes sākumā
^a
Pirmajam rakstzīmim jābūt mazajam burtam a
.^[0-9]
Pirmajam rakstzīmim jābūt ciparam.$
Saskaņošanai jānotiek virknes beigās
a$`` Pēdējam rakstzīmei jābūt mazajam burtam
a``.Priekšības tabula:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Paredzētie rakstzīmju saīsinājumi:
abr same as meaning
\d [0-9] Any single digit
\D [^0-9] Any single character that's not a digit
\w [a-zA-Z0-9_] Any word character
\W [^a-zA-Z0-9_] Any non-word character
\s [ \r\t\n\f] Any space character
\S [^ \r\t\n\f] Any non-space character
\n [\n] New line
1. piemērs: Palaist kā makro
Šajā makro piemērā tiek apskatīta vērtība šūnā A1
, lai noskaidrotu, vai pirmās 1 vai 2 zīmes ir cipari. Ja tas tā ir, tās tiek izņemtas un tiek parādīta pārējā virkne. Ja nē, tad tiek parādīts lodziņš, kurā tiek paziņots, ka nav atrasta atbilstība. Ailes A1
vērtība 12abc
atgriezīs abc
, vērtība 1abc
atgriezīs abc
, vērtība abc123
atgriezīs "Neatbilda", jo cipari nebija virknes sākumā.
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1")
If strPattern <> "" Then
strInput = Myrange.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
End Sub
2. piemērs: Palaist kā šūnu funkciju
Šis piemērs ir tāds pats kā 1. piemērs, bet ir iestatīts tā, lai darbotos kā šūnā esoša funkcija. Lai to izmantotu, mainiet kodu šādi:
Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strPattern = "^[0-9]{1,3}"
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
simpleCellRegex = regEx.Replace(strInput, strReplace)
Else
simpleCellRegex = "Not matched"
End If
End If
End Function
Ievietojiet savu virkni ("12abc") šūnā A1
. Ievadiet šo formulu =simpleCellRegex(A1)
šūnā B1
, un rezultāts būs "abc".
3. piemērs: Loop Through Range
Šis piemērs ir tāds pats kā 1. piemērs, bet tas veido cilpu caur šūnu diapazonu.
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A5")
For Each cell In Myrange
If strPattern <> "" Then
strInput = cell.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
Next
End Sub
4. piemērs: Dažādu rakstu sadalīšana
Šajā piemērā tiek veikta cilpa caur diapazonu (A1
, A2
& amp; A3
) un meklē virkni, kas sākas ar trim cipariem, kam seko viens burtu simbols un pēc tam 4 ciparu zīmes. Izvadā parauga sakritības tiek sadalītas blakus esošajās šūnās, izmantojot ()
. $1
ir pirmais raksts, kas atbilst pirmajam ()
kopumam.
Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A3")
For Each C In Myrange
strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
If strPattern <> "" Then
strInput = C.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
C.Offset(0, 1) = regEx.Replace(strInput, "$1")
C.Offset(0, 2) = regEx.Replace(strInput, "$2")
C.Offset(0, 3) = regEx.Replace(strInput, "$3")
Else
C.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
Rezultāti:
Papildu rakstu piemēri
String Regex Pattern Explanation
a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Single alpha, single digit, three alpha characters
a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} May or may not have preceeding alpha character
a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3} Single alpha, single digit, 0 to 3 alpha characters
a1aaa [a-zA-Z][0-9][a-zA-Z]* Single alpha, single digit, followed by any number of alpha characters
</i8> \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit
Lai izmantotu regulārās izteiksmes tieši Excel formulās, var noderēt šāda UDF (lietotāja definēta funkcija). Tā vairāk vai mazāk tieši atklāj regulārās izteiksmes funkcionalitāti kā Excel funkciju.
Tā pieņem 2-3 parametrus.
$0
, $1
, $2
utt. $0
ir visa sakritība, $1
un turpmāk atbilst attiecīgajām sakritības grupām regulārajā izteiksmē. Standarta noklusējuma vērtība ir $0
.E-pasta adreses iegūšana:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Rezultāti: [email protected]
Vairāku apakšvirsrakstu iegūšana:
=regex("Peter Gordon: som[email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Rezultāts: E-Mail: [email protected], Nosaukums: Pīters Gordons
:: Peter Gordon
Kombinētas virknes sadalīšana vienā šūnā tās sastāvdaļās vairākās šūnās:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Rezultāts: Peter Gordon
[email protected]
...
Lai izmantotu šo UDF, rīkojieties šādi (aptuveni balstoties uz šo Microsoft lapu. Tur ir laba papildu informācija!):
ALT+F11
, lai atvērtu Microsoft Visual Basic for Applications redaktoru.Regex
un funkcijas regex
nosaukuma piešķiršana izraisa #NAME! kļūdas).
4. Lielajā teksta logā vidū ievietojiet šādu tekstu:
. 4. As Variant
Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
Dim replaceNumber As Integer
With inputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = matchPattern
End With
With outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)"
End With
Ar outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set inputMatches = inputRegexObj.Execute(strInput)
If inputMatches.Count = 0 Then
regex = False
Else
Set replaceMatches = outputRegexObj.Execute(outputPattern)
For Every replaceMatch In replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" & replaceNumber
Ja replaceNumber = 0 Tad
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Pretējā gadījumā
Ja replaceNumber > inputMatches(0).SubMatches.Count Tad
'regex = "Atrasts no A līdz augstam $ tagam. Lielākā atļautā ir $" & inputMatches(0).SubMatches.Count & "."
regex = CVErr(xlErrValue)
Iziet no funkcijas
Citādi
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
End If
End If
Next
regex = outputPattern
End If
Funkcijas beigas
[2]: .: /questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#answer-22542835
Šeit ir mans mēģinājums:
Function RegParse(ByVal pattern As String, ByVal html As String)
Dim regex As RegExp
Set regex = New RegExp
With regex
.IgnoreCase = True 'ignoring cases while regex engine performs the search.
.pattern = pattern 'declaring regex pattern.
.Global = False 'restricting regex to find only first match.
If .Test(html) Then 'Testing if the pattern matches or not
mStr = .Execute(html)(0) '.Execute(html)(0) will provide the String which matches with Regex
RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
Else
RegParse = "#N/A"
End If
End With
End Function