dj44
06-17-2017, 09:32 AM
folks,
good saturday
now i found some regex to extract between 2 pipes
| hello | and some text ....
| my data | rest of text
I am trying to insert this formula into the column but i have fallen into difficulty
Sub Insert_Formulas()
Dim LstRw As Long, Rng As Range
LstRw = Cells(Rows.Count, "G").End(xlUp).Row
For Each Rng In Range("G6:G" & LstRw)
Rng.Formula = "=RegexExtract("F6:F",""\|(.*?)\|"")" << here i enetred the cells
Next Rng
End Sub
Function RegexExtract(ByVal text As String, _
ByVal extract_what As String, _
Optional separator As String = ", ") As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String
RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
For i = 0 To allMatches.Count - 1
For j = 0 To allMatches.Item(i).submatches.Count - 1
result = result & (separator & allMatches.Item(i).submatches.Item(j))
Next
Next
If Len(result) <> 0 Then
result = Right$(result, Len(result) - Len(separator))
End If
RegexExtract = result
End Function
'https://stackoverflow.com/questions/7086270/how-to-extract-text-within-a-string-of-text
I just want to copy the formula into column G
Its reference column is Column F
please may a regex expert have a look at the range problem, i entered the cells but it doesnt work
how can i enter this regexcular formula
good saturday
now i found some regex to extract between 2 pipes
| hello | and some text ....
| my data | rest of text
I am trying to insert this formula into the column but i have fallen into difficulty
Sub Insert_Formulas()
Dim LstRw As Long, Rng As Range
LstRw = Cells(Rows.Count, "G").End(xlUp).Row
For Each Rng In Range("G6:G" & LstRw)
Rng.Formula = "=RegexExtract("F6:F",""\|(.*?)\|"")" << here i enetred the cells
Next Rng
End Sub
Function RegexExtract(ByVal text As String, _
ByVal extract_what As String, _
Optional separator As String = ", ") As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String
RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
For i = 0 To allMatches.Count - 1
For j = 0 To allMatches.Item(i).submatches.Count - 1
result = result & (separator & allMatches.Item(i).submatches.Item(j))
Next
Next
If Len(result) <> 0 Then
result = Right$(result, Len(result) - Len(separator))
End If
RegexExtract = result
End Function
'https://stackoverflow.com/questions/7086270/how-to-extract-text-within-a-string-of-text
I just want to copy the formula into column G
Its reference column is Column F
please may a regex expert have a look at the range problem, i entered the cells but it doesnt work
how can i enter this regexcular formula