Mac – excel vba – multiline regex not working

macrosmicrosoft excelregexvba

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

select (.*?)[\r\n|\s]from