From: bbiker <renard3411@...>
Date: 2007-07-19T06:45:03+09:00
Subject: Re: regex help please - parsing an Excel Range

On Jul 17, 8:52 am, Todd Burch <pro...@burchwoodusa.com> wrote:
> Phil Meier wrote:
> > You can have this in one RegEx:
>
> > addr = "$G$28:$I$28"
> > addr =~ /:\$*([A-Z])+\$*(.*)$/
> > puts "row=#{$2}, col=#{$1}"   # -> row=28, col=I
>
> > An addr = "G28:I28" would also be accepted by the RegEx.
>
> Works perfect Phil.  Thanks!  Todd
>
> --
> Posted viahttp://www.ruby-forum.com/.


For your first example, assuming that the cells contain 1..6, 8..13
with cell B2..B3 merged and cells D1..D4 are also merged.
you can simply obtain the values, you get the left most value of a
block of merged cells and nil for the remaining cells

values = ws.Range("A1:D4")['Value']

p values => [[1.0, 2.0, 3.0, 4.0], [5.0, 6.0, nil, 8.0], [9.0, 10.0,
11.0, 12.0], [13.0, nil, nil, nil]]

ps values =>
1.0
2.0
3.0
4.0
5.0
6.0
nil
8.0
9.0
10.0
11.0
12.0
13.0
nil
nil
nil

Note that if the last row did not have any merged cell you would have
gotten row 4, col D
and been unware of the B2..B3 merge.