Insert Regex Formula into Column

[dj44]

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

[mdmackillop]

For Each Rng In Range("G6:G" & LstRw)
Rng.FormulaR1C1 = "=RegexExtract(RC[-1],""\|(.*?)\|"")"
Next Rng

[dj44]

Hello M,

well i had no choice today i had to get the big guns out and try some regex although i really
try to avoid it.

These text were very stubborn so i had to enclose it in pipes and brackets


Well i recorded a macro and i still couldnt make it work. I had to change something in the function :eek:

but after a sizeable duel, this did the trick so im happy i dont have to type in that regex in the cells




For Each Rng In Range("G6:G9")
Rng.FormulaR1C1 = "=RegexExtract(RC[-1],""\|(.*?)\|"")"
Next Rng




well what a nice day it is and hope all are having a nice weekend

cheers

:beerchug:

[mdmackillop]

If you're extracting between pipes or similar then Split would be simpler.


For Each Rng In Range("G6:G" & LstRw)
Rng.FormulaR1C1 = "=DoSplit(RC[-1],""|"",1)"
Next Rng

Function DoSplit(data, sep, index)
DoSplit = Split(data, sep)(index)
End Function