# Regex help please - parsing an Excel Range

**URL:** <https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594>\
**Category:** Ruby\
**Created:** [July 17, 2007, 6:55am UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594 "2007-07-17T06:55:08Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![toadkicker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5fc32e/32.png) [@toadkicker](https://www.ruby-forum.com/u/toadkicker)\
**Post date:** [July 17, 2007, 6:55am UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594/1 "2007-07-17T06:55:08Z")

</div>

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  
A1:D4.

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 A4:D4 in my tiny worksheet, the  
SpecialCells(xlCellTypeLastCell) return A4:D4. 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

---

<div class="post-metadata">

**Author:** ![toadkicker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5fc32e/32.png) [@toadkicker](https://www.ruby-forum.com/u/toadkicker)\
**Post date:** [July 17, 2007, 9:03am UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594/2 "2007-07-17T09:03:06Z")

</div>

Todd B. wrote:

> 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.

---

<div class="post-metadata">

**Author:** ![toadkicker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5fc32e/32.png) [@toadkicker](https://www.ruby-forum.com/u/toadkicker)\
**Post date:** [July 17, 2007, 2:50pm UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594/3 "2007-07-17T14:50:39Z")

</div>

Phil M. 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

---

<div class="post-metadata">

**Author:** ![toadkicker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5fc32e/32.png) [@toadkicker](https://www.ruby-forum.com/u/toadkicker)\
**Post date:** [July 18, 2007, 6:01am UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594/4 "2007-07-18T06:01:36Z")

</div>

On Jul 16, 10:55 pm, Todd B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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 )

---

<div class="post-metadata">

**Author:** ![toadkicker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/5fc32e/32.png) [@toadkicker](https://www.ruby-forum.com/u/toadkicker)\
**Post date:** [July 18, 2007, 11:46pm UTC](https://www.ruby-forum.com/t/regex-help-please-parsing-an-excel-range/109594/5 "2007-07-18T23:46:47Z")

</div>

On Jul 17, 8:52 am, Todd B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> –  
> 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.