CODEWITHSUNDEEP
rubyexcel
Kokizzu
Kokizzu

Reputation: 26888

Convert excel column letter to integer in Ruby

What's are the simplest way to convert excel-like column letter to integer?

for example:

AB --> 27
AA --> 26
A --> 0
Z --> 25

Upvotes: 7

Views: 1520

Answers (5)

Phrogz
Phrogz

Reputation: 303361

def excel_col_index( str )
  value = Hash[ ('A'..'Z').map.with_index.to_a ]
  str.chars.inject(0){ |x,c| x*26 + value[c] + 1 } - 1
end

Or

def excel_col_index( str )
  offset = 'A'.ord - 1
  str.chars.inject(0){ |x,c| x*26 + c.ord - offset } - 1
end

Upvotes: 13

Tim Diggins
Tim Diggins

Reputation: 4506

here's a version of the accepted answer, with a spec:

RSpec.describe "#excel_col_index" do
  def excel_col_index(str)
    value = Hash[('A'..'Z').map.with_index.to_a]
    str.chars.inject(0) { |x, c| x * 26 + value[c] + 1 } - 1
  end

  { "A" => 0, "Z" => 25, "AB" => 27, "AA" => 26 }.each do |col, index|
    it "generates #{index} from #{col}" do
      expect(excel_col_index(col)).to eq(index)
    end
  end
end

(but I'll also edit the accepted answer to have the required - 1)

Upvotes: 3

Martijn
Martijn

Reputation: 1719

This is one of those bits that you can keep iterating on for a long time. I ended up with this:

"AB1".each_codepoint.reduce(0) do |sum, n|
  break sum - 1 if n < 'A'.ord  # reached a number
  sum * 26 + (n - 'A'.ord + 1)
end # => 27

From the xsv source code

Upvotes: 1

spickermann
spickermann

Reputation: 106972

I would do something like this:

def column_name_to_number(column_name)
  multipliers = ('A'..'Z').to_a
  chars = column_name.split('')

  chars.inject(-1) { |n, c| multipliers.index(c) + (n + 1) * 26 }
end

Upvotes: 5

Kokizzu
Kokizzu

Reputation: 26888

ah nevermind..

def cell2num col
  val = 0
  while col.length > 0
    val *= 26
    val += (col[0].ord - 'A'.ord + 1)
    col = col[1..-1]
  end
  return val - 1
end

Upvotes: 2

Related Questions