Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Function simpleCellRegex(Myrange As Range) As String
- Dim regEx As Object
- Set regEx = CreateObject("VBScript.RegExp")
- Dim strPattern As String
- Dim strInput As String
- Dim strReplace As String
- Dim strOutput As String
- strPattern = "[Γ -ΓΏ]+"
- 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
- '//////////////////////////////////////////////////////////////
- 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
- '//////////////////////////////////////
- 'This example is the same as example 1 but loops through a range of cells.
- 'Example 3: Loop Through Range
- '////////////////////////
- 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
- '///////////////////////////////////////////////////////
- 'Example 4: Splitting apart different patterns
- 'This example loops through a range (A1, A2 & A3) and looks for a string starting with three digits followed by a single alpha character and then 4 numeric digits. The output splits apart the pattern matches into adjacent cells by using the (). $1 represents the first pattern matched within the first set of ().
- '/////////////////////////////////////////////////////////
- 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
Add Comment
Please, Sign In to add comment