Regular Expression pattern help needed!

[BHPexpert]

Regular Expression help needed
--------------------------------------------------------------------------------

I want to extract all text that is contained inside the brackets after the word INDIRECT. There must be at least one pair of parentheses, they may be multiple pairs

for exampple, this string

'=IF($B$9="Off",0,INDIRECT(ADDRESS(1(8),COLUMNS($A $41:C41),,,$B$9 )),()aaaa'

has four bracket pairings until the initial "(" is closed out

so the Regexp woul return

INDIRECT(ADDRESS(1(8),COLUMNS($A$41:C41),,,$B$9))

Thanks

[miller]

#!/usr/bin/perl
 
our $parenthesis = qr{
    \(
    (?:
        (?> [^()]+ )
    |
        (??{ $parenthesis })
    )*
    \)
}sx;
 
our $indirectContents = qr{
    INDIRECT\(
    (
        (?:
            (?> [^\(\)]+ )
        |
            (??{ $parenthesis })
        )*
    )
    \)
}sx;
 
my $str = '=IF($B$9="Off",0,INDIRECT(ADDRESS(1(8),COLUMNS($A$41:C41),,,$B$9 )),()aaaa';
 
print "String = $str\n";
 
if ($str =~ m{$indirectContents}) {
    print "Contents = $1\n";
}
 
1;
 
__END__
 

What you've requested is actually the most advanced thing that perl regular expression allow you to do. I leave it up to you to look up all the resources to explain why the above code works, as I don't even remember where I originally learned how to do this. This code currently prints out what is inside INDIRECT(.*). If you want to include the text "INDIRECT", simply move lines 15 and 21 to 14 and 22 respectively.