2

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
;
0

3 Answers 3

5

Your problem is probably because . doesn't match newlines (I'm not sure, but .MultiLine should have changed that behaviour? For whatever reason, it might not have, so try this.).

Try:

select.+\n*from

Or maybe:

select.+\r\n*from

Even better:

select[\s\S]+from

Also:

select[\u0000-\uFFFF]+from

The first two mean match everything except newlines, and also match newlines at a specific position. The last two mean match everything including newlines.

1
  • 2
    +1 for select[\s\S]+from.
    – brettdj
    Mar 11, 2012 at 5:31
4

Try with this regex

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

There are two issues:

  1. The regex pattern does not match the New Line ( as others have mentioned): use
    objRegExp.Pattern = "select\s.+\sfrom" (\s to match whitespace, including new lines)
  2. Change the replace pattern to "select 1" & vbNewLine & "from" to insert the new line where you want it

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .