Function uuu$(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "(\d{10})(\d{2})(\d{3})(\d{4})": .Global = True
uuu = .Replace(t, "$1:$2:$3:$4")
End With
End Function
Sub test2()
Dim t$, i&: t = Range("B4")
With CreateObject("VBScript.RegExp"): .Pattern = "<(.+?)>": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("B8").Offset(i).Value = .Execute(t)(i).Submatches(0): Next
End With
End Sub
Sub test()
Dim t$, i&: t = Range("A1")
With CreateObject("VBScript.RegExp"): .Pattern = "<(.+?)>": .Global = True
For i = 0 To .Execute(t).Count - 1: Range("B1").Offset(, i).Value = .Execute(t)(i).Submatches(0): Next
End With
End Sub
вариант макроса,соответствующего функции vvv,кнопки test и повтор,лист Лист3 вспомогательный для демонстрации повтора.
Код
Sub test1()
Dim z, t$, t1$, i&: z = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
With CreateObject("VBScript.RegExp"): .IgnoreCase = True
For i = 1 To UBound(z): t = z(i, 1): t1 = uuu(t): .Pattern = "^.+(?=" & t1 & ")"
z(i, 1) = .Replace(t, "")
Next
Range("B1").Resize(UBound(z), 1).Value = z
End With
End Sub
Код
Function uuu(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "[а-яё]{3,}": .IgnoreCase = True
uuu = .Execute(t)(0)
End With
End Function
Function zzz(t$)
With CreateObject("Scripting.Dictionary"): .CompareMode = 1
For Each t1 In Split(t, "-"): .Item(Trim(t1)) = 0: Next
zzz = Left(Join(.Keys, "-"), Len(Join(.Keys, "-")) - 1)
End With
End Function
Function uuu$(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "[^:]+": .Global = True
uuu = "|" & Trim(.Execute(t)(.Execute(t).Count - 1))
End With
End Function
Function vvv(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "на общую сумму (\d+) рублей"
If .test(t) Then vvv = .Execute(t)(0).Submatches(0) Else vvv = t
End With
End Function
еще вариант в виде функции в D5 :без Submatches,со считыванием в массив
Код
Function vvv#(r As Range)
Dim z: z = r.Value
With CreateObject("VBScript.RegExp"): .Pattern = "\d+\.\d+"
For i = 1 To UBound(z): t = z(i, 1)
If .test(t) Then vvv = vvv + Replace(.Execute(t)(0), ".", ",")
Next
End With
End Function
вариант макроса,соответствующего #6 и выложенному файл-примеру,кнопки test и повтор
Код
Sub test()
Dim z, t$, i&: z = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(z): t = z(i, 1)
z(i, 1) = StrReverse(Split(StrReverse(t), "-", 2)(1)) & Chr(32) & StrReverse(Split(StrReverse(t), "-", 2)(0))
Next
Range("A3").Resize(UBound(z), 1).Value = z
End Sub
Function uuu$(t$)
Dim t1$
With CreateObject("VBScript.RegExp"): .Pattern = "(\+7|[-\d\s\)\(]+"
If .test(t) Then t1 = .Execute(t)(0) Else t1 = ""
.Pattern = "[-\(\)\s]": .Global = True
t1 = .Replace(t1, ""): .Pattern = "(\+7|8)\d{10}"
If .test(t1) Then uuu = .Execute(t1)(0) Else uuu = ""
End With
End Function
вариант функции в I2 и макрос с выводом результата в столбце A, кнопки test и повтор.
Код
Function vvv$(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "^.+(?=\./)"
vvv = .Execute(t)(0)
End With
End Function
Код
Sub test()
Dim z, t$, i&: z = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
With CreateObject("VBScript.RegExp"): .Pattern = "^.+(?=\./)"
For i = 1 To UBound(z): t = z(i, 1): z(i, 1) = .Execute(t)(0): Next
Range("A2").Resize(UBound(z), 1).Value = z
End With
End Sub
Function vvv(t$)
Dim t1$
With CreateObject("VBScript.RegExp"): .Pattern = " \d+ $"
t1 = .Execute(t)(.Execute(t).Count - 1)
.Pattern = "^.+(?=" & t1 & ")"
If .test(t) Then vvv = .Execute(t)(0) Else vvv = t
End With
End Function
Function uuu$(t$)
Dim i%, t1$
With CreateObject("VBScript.RegExp"): .Global = True: .IgnoreCase = True
.Pattern = "(?:[^-а-яё\d]|^)[-а-яё\d]{1,2}(?=[^-а-яё\d]|$)"
t1 = .Replace(t, "$&+"): uuu = Replace(t1, "+ ", "+")
End With
End Function
Код
Function yyy$(t$)
Dim i%
With CreateObject("VBScript.RegExp"): .Global = True: .IgnoreCase = True
.Pattern = "(?:[^-а-яё\.,\d]|^)[-а-яё\.,\d]{1,2}(?=[^-а-яё\.,\d]|$)"
yyy = .Replace(t, "$&+")
End With
End Function
еще вариант функции и соответствующий ей макрос,кнопки test и повтор
Код
Function vvv(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "г"
vvv = CDate(.Replace(t, ""))
End With
End Function
Код
Sub test()
Dim z, t$, i&: z = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row).Value
With CreateObject("VBScript.RegExp"): .Pattern = "г"
For i = 1 To UBound(z): t = z(i, 1)
z(i, 1) = CDate(.Replace(t, ""))
Next
Range("E1").Resize(UBound(z), 1).Value = z
End With
End Sub
вариант макроса с результатом в столбце A,кнопки test и повтор,функция vvv в столбце F
Код
Sub test()
Dim z, t$, i&: z = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
With CreateObject("VBScript.RegExp"): .Pattern = "[^,]+"
For i = 1 To UBound(z): t = z(i, 1)
z(i, 1) = .Execute(t)(0)
Next
Range("A1").Resize(UBound(z), 1).Value = z
End With
End Sub
Function vvv$(t$)
With CreateObject("VBScript.RegExp"): .Global = True: .IgnoreCase = True
.Pattern = "(?:[^а-яё\w]|^)[а-яё\w]+(?=[^а-яё\w]|$)"
vvv = Trim(.Execute(t)(1))
End With
End Function
Function vvv$(t$)
Dim t1$
With CreateObject("VBScript.RegExp"): .Pattern = "[а-яё]+ [а-яё]+": .IgnoreCase = True
t1 = .Execute(t)(0): .Pattern = "\d+%": vvv = .Execute(t)(0) & " - " & t1
End With
End Function