Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
;
share|improve this question
add comment

3 Answers

Try with this regex

select (.*?)[\r\n|\s]from
share|improve this answer
add comment

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.

share|improve this answer
    
+1 for select[\s\S]+from. –  brettdj Mar 11 '12 at 5:31
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.