RegExp, find and replace

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,912
Office Version
  1. 365
Platform
  1. Windows
Hi All

I'm a bit rusty on the ol' regexp and I think I have been trying too hard for too long so I've lost the plot. Any help appreciated...

I have a string in cell E11:
Code:
[PLAIN]||=IF($B$8,< ISIN > & " " & IF(< TYPE >="Bond",IF(ISBBERR(BDP(< ISIN > & " Corp",SECURITY_NAME)), "Mtge","Corp"),IF(< ISIN >="Equity","Equity")),FALSE)||[/PLAIN]

Find each sub-string that is like "< ?* >" (excluding spaces!)
Look for the contents (e.g. ISIN) in row 10
Replace the entire sub-string with the cell address that refers to row 11 and the column index where the sub-string was found in row 10

So to give an example... Column labels in row10. ISIN is in column A, TYPE is in G. The result should be:
Code:
||=IF($B$8,[B][COLOR="Red"]A11[/COLOR][/B] & " " & IF([B][COLOR="Red"]G11[/COLOR][/B]="Bond",IF(ISBBERR(BDP([B][COLOR="Red"]A11[/COLOR][/B] & " Corp","SECURITY_NAME")), "Mtge","Corp"),IF([B][COLOR="Red"]A11[/COLOR][/B]="Equity","Equity")),FALSE)||

Edit: < ISIN > and such substrings don't actually contain any spaces. I put these in to prevent it from being parsed.
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Not sure if the below code helps but it might. It only fixes the value in cell E11, but it's easy to get it to work on an entire range. There is no string clean up or error checking as well. I know the two loops are bad but...... ;)

Sub RegExStuff()
Dim RegEx As New regexp
Dim strTest As String
Dim i As Long
Dim ws As Worksheet
Dim objMatch As Object

Set ws = ActiveSheet

RegEx.Global = False 'find only one match at a time
RegEx.Pattern = "<\w*>" 'any number of capital or lower case letters or numbers enclosed in < >

Do
strTest = ws.Range("E11").Value 'get search string
If RegEx.Test(strTest) Then
Set objMatch = RegEx.Execute(strTest) 'get first match

'loop through headers in row 10
i = 1
Do
'check if header matches search string
If "<" & ws.Cells(10, i).Value & ">" = objMatch(0) Then
'replace value with address
ws.Range("E11").Value = RegEx.Replace(strTest, Replace(ws.Cells(10, i).address, "$", ""))
Exit Do
Else
'to avoid endless loop
If i = 100 Then
Exit Do
Else
i = i + 1
End If
End If
Loop
Else
Exit Do
End If
Loop
End Sub
 
Upvote 0
Wonderful, thanks vaskov! :)

It was the Execute method that you have used and that I overlooked and so desperately needed. So I made few modifications in order to embed it into my project, the most fundamental change being:

If you change Global property to True and then call the Execute method, objMatch with then store an item for each component (since objMatch is a Collection object). You can then optimise further by looping through each item in the collection (using For Each, which is the fasted method of looping through a collection), performing the substitute on each with the cell reference address found.

Thanks again mate. Your help is just what I needed to get myself back on track. :beerchug:
 
Upvote 0

Forum statistics

Threads
1,224,352
Messages
6,178,066
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top