In cell A1 of an excel sheet I have the following contents inside the cell:
select a.id, b.type, c.name
from blah a,
blah2 b,
blah3 c
where a.id = b.key
and b.key = c.id
;
Either of the below are desired results:
select 1 from
blah a,
blah2 b,
blah3 c
where a.id = b.key
and b.key = c.id
;
OR
select 1
from blah a,
blah2 b,
blah3 c
where a.id = b.key
and b.key = c.id
;
I have this vba code
Dim objRegExp As New RegExp
objRegExp.Pattern = "select .+ from"
objRegExp.Global = True
objRegExp.MultiLine = True
regexpReplace =objRegExp.Replace(Trim(Worksheets("new").Range("A1").Value),"select 1 from")
MsgBox regexpReplace
But the message box is displaying the exact text from cell A1 (ie with no replacement taking place). Does anyone know why this is happening? I wan't to be able to replace across multilines.
I know my regex code is ok because I tried changing cell A1 to the below (and it did the replacement properly):
select a.id, b.type, c.name from
blah a,
blah2 b,
blah3 c
where a.id = b.key
and b.key = c.id
;
Best Answer
Try with this regex