Regex-like parsing

[Roderick_E]

Hi old friends, been a while since I've been on here; they got me learning python at work. Anyhow, I'm trying to create a VBA with regex-like abilities.

I want to look at a string and parse all <*> (ie, <someword>) and replace it with the word as a variable.

Example:
Hello <username>,
We are glad you are posting to Mr.Excel on <date>

I want to parse and find all uses of <> and in those places used the defined variable within the VBA


VBA sample:

username = "Bob"
datestr = format(now(),"Short Date")
regexstr = replace(replace(regexstr,"<username>",username),"<date>",datestr)

----
However I don't want to define each and every place to replace so I need to loop through the regexstr and find any place with <...> and replace with it's variable if defined otherwise just leave the <...> in place.
Help please?

 

[Roderick_E]

This works at replacing the <> but does not do anything to turn what remains into a variable.

Function parsebetween(S As String) As String
parsebetween = Replace(Replace(S, "<", ""), ">", "")
End Function

Sub testit()
test = "bob"
MsgBox parsebetween("This is a <test><test>")
End Sub

</test>

 

[shg]

Can you give an example of what you're trying to do?

 

[Roderick_E]

Can you give an example of what you're trying to do?

Yes,

Suppose in a cell (more than likely merged) a person types in:

Hello <username>, Please see invoice <invoicenum> attached.

I want to be able to replace all the places with <...> with the dynamic variable for that "tag"

So the result might be:

Hello Bob Smith, Please see invoice 1234 attached.

But I also want the code to by flexible enough that I can add more variables easily, such as:

username = cells(x,y)
invoicenum = cells(x,z)
newvar = cells(x,aa)

So that I can easily allow the user to change the original string and process it correctly.

Hello <username>, Please see invoice <invoicenum> attached. Thanks <newvar>.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

variable​

[/td][td="bgcolor:#F3F3F3"]

value​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]username[/td][td]Bob Smith[/td][td][/td][td]Hello, <username>, please pay invoice <invoicenum> PDQ.[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]invoicenum[/td][td]

12345​

[/td][td][/td][td="bgcolor:#CCFFCC"]Hello, Bob Smith, please pay invoice 12345 PDQ.[/td][td="bgcolor:#CCFFCC"]D3: =TagRepl(D2, A2:B3)[/td][/tr]
[/table]

The > and < characters are stripped by the board, but they're there.

Function TagRepl(sInp As String, rFindRep As Range) As String
  Dim cell As Range
  
  TagRepl = sInp
  
  For Each cell In Intersect(rFindRep.Columns(1).Cells, Application.ThisCell.Worksheet.UsedRange)
    TagRepl = Replace(TagRepl, "<" & cell.Value & ">", cell.Offset(, 1).Value)
  Next cell
End Function

 

[Rick Rothstein]

Does this UDF (user defined function) do what you want...

[table="width: 500"]
[tr]
	[td]Function TagReplace(ByVal Txt As String, ParamArray FindCommaReplaceWith() As Variant) As String
  Dim N As Long
  For N = LBound(FindCommaReplaceWith) To UBound(FindCommaReplaceWith) Step 2
    Txt = Join(Split(Txt, "<" & FindCommaReplaceWith(N) & ">", , vbTextCompare), FindCommaReplaceWith(N + 1))
  Next
  TagReplace = Txt
End Function[/td]
[/tr]
[/table]

The first argument is the text with the angle brackets in it, following that is a comma delimited list where the list consists of "first variable", "replace text", "second variable", "replace text", etc. So, for your example, assuming the text with the angle brackets is in cell A1, and your text to find is listed in Column B starting at row 1 and their replacements are in Column C, you would call it from other VB code like this...
[table="width: 500"]
[tr]
[td]

ReplacedText = TagReplace(Range("A1"), Range("B1"), Range("C1"), Range("B2"), Range("C2"),Range("B3"), Range("C3"))

[/td]
[/tr]
[/table]

 

[Roderick_E]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

variable​

[/TD]
[TD="bgcolor: #F3F3F3"]

value​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]username[/TD]
[TD]Bob Smith[/TD]
[TD][/TD]
[TD]Hello, <username>, please pay invoice <invoicenum> PDQ.</invoicenum></username>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]invoicenum[/TD]
[TD]

12345​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]Hello, Bob Smith, please pay invoice 12345 PDQ.[/TD]
[TD="bgcolor: #CCFFCC"]D3: =TagRepl(D2, A2:B3)[/TD]
[/TR]
</tbody>[/TABLE]


The > and < characters are stripped by the board, but they're there.

Function TagRepl(sInp As String, rFindRep As Range) As String
  Dim cell As Range
  
  TagRepl = sInp
  
  For Each cell In Intersect(rFindRep.Columns(1).Cells, Application.ThisCell.Worksheet.UsedRange)
    TagRepl = Replace(TagRepl, "<" & cell.Value & ">", cell.Offset(, 1).Value)
  Next cell
End Function

Hmm returned a zero -- let me try again
Nevermind -- it worked thanks much. Now I have to translate it into a VBA reference rather than cells. I should be able to figure that much out.