regex help please - parsing an Excel Range

[Todd Burch]

I'm parsing a worksheet in EXCEL, working around (what I consider to be)
unexpected results when merged cells are the last thing on a worksheet.

For conversation sake, lets say my tiny worksheet's whole used range is
A14.

I'm use:

lastcell = sheet.Cells.SpecialCells(xlCellTypeLastCell)

to get the last cell in the sheet, and subsequently I parse out the
maximum row and maximum column used in the worksheet

lastcell.Address # -> $D$4
maxrow = lastcell.Row # -> 4
maxcol = lastcell.Column # -> 4 A.K.A. "D"

This works great most of the time. However, when there is a Merged cell
that goes from A44 in my tiny worksheet, the
SpecialCells(xlCellTypeLastCell) return A44. This seems ok at first,
until...

lastcell.Address # -> $A$4:$D$4
maxrow = lastcell.Row # -> 4 as epxected
maxcol = lastcell.Column # -> 1 A.K.A. "A" - this is not expected!

Therefore, I want to parse out the back half of the range (the $D$4) to
get the actual last row and last column when lastcell.MergeCells is
true.

I've worked this novice regex series out (using a different example),
but I'm thinking it could much more elegant:

addr = "$G$28:$I$28" # merged cell range
addr =~ /:\$/ # isolates the "I$28" into $'
back = $' # puts "I$28" into a variable
back =~ /\$/ # "I" and "28" go into $` and $' respectively
puts "row=#{$'}, Col=#{$`}"; # -> Row=28, Col=I - Bingo!

I then simply let EXCEL convert I to 9. (or AA to 27, etc...)

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

Thanks, Todd

 

[Phil Meier]

addr = "$G$28:$I$28" # merged cell range
addr =~ /:\$/ # isolates the "I$28" into $'
back = $' # puts "I$28" into a variable
back =~ /\$/ # "I" and "28" go into $` and $' respectively
puts "row=#{$'}, Col=#{$`}"; # -> Row=28, Col=I - Bingo!

I then simply let EXCEL convert I to 9. (or AA to 27, etc...)

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

Thanks, Todd

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.

 

[Todd Burch]

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

 

[Phrogz]

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

_,col,row = /(\w+)\$?(\d+)$/.match( my_string )

 

[bbiker]

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

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("A14")['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.