2

I am coding in Ruby / Rails, and I have a cell range, an example is:

[0,1]..[0,3] with the convention [row, column]..[row..column]

Excel uses cell ranges like:

A2:A4 with the convention <column><row>:<column><row>

I need to convert from the former to the latter. It is mainly the letter's that I am concerned with:

A => 0,
B => 1,
C => 2,
AA => 26
AB => 27

How would I go about doing that?

I essentially need to do the reverse of this:

Generate letters to represent number using ruby?

Community
  • 1
  • 1
Steve
  • 2,764
  • 4
  • 27
  • 32

2 Answers2

0

VBA Solution

Sub test_JohnLinux()

MsgBox ColLet(4) & vbCrLf & InvColLet("D")

End Sub

Get number from letters :

Public Function InvColLet(x As String) As Long
ActiveSheet.Cells(1, x).EntireColumn.Column
End Function

Get letters from numbers :

Public Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Apologies, I don't think I was clear enough that I am looking for a Ruby solution. – Steve Nov 26 '15 at 13:09
  • No problem, I had this already done as I use it pretty often in Excel! If it is ok with you, I'll let the answer here! – R3uK Nov 26 '15 at 13:30
0

Ruby Solution

Credit must go to Howard, I adapted the answer from here: https://codegolf.stackexchange.com/questions/3971/generate-excel-column-name-from-index

excel_column_ref =-> array_column_ref do
  ref = "A"
  array_column_ref.times{ ref.next! }
  ref
end

This returns a proc.

Usage excel_column_ref[26] = 'AA'

Community
  • 1
  • 1
Steve
  • 2,764
  • 4
  • 27
  • 32