excel_alpha.rb

# https://gist.github.com/atheiman/2b359b3da6a80fc0a6c8d54c78abfc5b
# 
# Write a function (with helper functions if needed) called to Excel that takes
# an excel column value (A,B,C,D…AA,AB,AC,… AAA..) and returns a corresponding
# integer value (A=1,B=2,… AA=27..).

module ExcelAlpha
  ALPHA_HASH = Hash[('A'..'Z').to_a.zip (1..26).to_a].freeze
  DECIMAL_HASH = ALPHA_HASH.invert.freeze
  
  module_function

  def simple_excel_to_decimal(str)
    # 26 for each preceeding 'A' + the value of the last char
    (str.length - 1) * 26 + ALPHA_HASH[str[-1].upcase]
  end

  def complex_excel_to_decimal(str)
    # base 26 math rather than base 10, think converting hexadecimal or binary to decimal
    # 
    # length - 0 col increases are 1 ( 26 ^ 0 )
    # length - 1 col increases are 26 ( 26 ^ 1 )
    # length - 2 col increases are 676  ( 26 ^ 2 )
    # 'ABC' #=> (ALPHA_HASH['A'] * 26 ** 2) +
    #           (ALPHA_HASH['B'] * 26 ** 1) +
    #           (ALPHA_HASH['C'] * 26 ** 0)
    # 'ABC' #=> 676 + 52 + 3
    # 'ABC' #=> 731
    col = 0
    str.to_s.upcase.chars.reverse.each_with_index do |char, idx|
      col += ALPHA_HASH[char] * 26 ** idx
    end
    col
  end

  def decimal_to_complex_excel(int)
    excel = []
    until int.zero?
      int, rem = int.divmod 26
      excel << rem
    end
    excel.reverse.map { |i| DECIMAL_HASH[i] }.join
  end
end


require 'rspec'

describe ExcelAlpha do
  context 'ALPHA_HASH' do
    it 'has a correct mapping of letters to alphabetical index' do
      expect(described_class::ALPHA_HASH).to eq(
        "A"=>1, "B"=>2, "C"=>3, "D"=>4, "E"=>5, "F"=>6, "G"=>7, "H"=>8, "I"=>9,
        "J"=>10, "K"=>11, "L"=>12, "M"=>13, "N"=>14, "O"=>15, "P"=>16, "Q"=>17,
        "R"=>18, "S"=>19, "T"=>20, "U"=>21, "V"=>22, "W"=>23, "X"=>24, "Y"=>25,
        "Z"=>26
      )
    end
  end

  context '#simple_excel_to_decimal' do
    {
      'A' => 1,
      'B' => 2,
      'L' => 12,
      'Z' => 26,
      'AA' => 27,
      'AB' => 28,
      'AC' => 29,
      'AAA' => 53,
      'AAB' => 54,
      'AAC' => 55,
      'AAAA' => 79,
      'AAAB' => 80,
      'AAAC' => 81,
    }.each do |col, int|
      it "converts Excel column label '#{col}' to '#{int}'" do
        expect(described_class.simple_excel_to_decimal(col)).to eq(int)
      end
    end
  end

  complex_excel_values = {
    'A' => 1,
    'B' => 2,
    'L' => 12,
    'Y' => 25,
    'Z' => 26,
    'AA' => 27,
    'AB' => 28,
    'BA' => 53,
    'BB' => 54,
    'HQ' => 225,
    'MM' => 351,
    'SI' => 503,
    'ZZ' => 702,
    'AAA' => 703,
    'AAB' => 704,
    'ABA' => 729,
    'ABB' => 730,
    'DJW' => 2987,
    'ZZZ' => 18278,
    'AAAA' => 18279,
    'AAAB' => 18280,
    'AABA' => 18305,
    'AABB' => 18306,
    'ABAA' => 18955,
    'ABAB' => 18956,
    'COWN' => 63480,
    'IHQU' => 164055,
    'ZZZZ' => 475254
  }

  context '#complex_excel_to_decimal' do
    complex_excel_values.each do |col, int|
      it "converts Excel column label '#{col}' to '#{int}'" do
        expect(described_class.complex_excel_to_decimal(col)).to eq(int)
      end
    end
  end

  context '#decimal_to_complex_excel' do
    complex_excel_values.each do |col, int|
      it "converts '#{int}' to Excel column label '#{col}'" do
        expect(described_class.decimal_to_complex_excel(int)).to eq(col)
      end
    end
  end
end

rspec.out

$ rspec excel_alpha.rb

ExcelAlpha
  ALPHA_HASH
    has a correct mapping of letters to alphabetical index
  #simple_excel_to_decimal
    converts Excel column label 'A' to '1'
    converts Excel column label 'B' to '2'
    converts Excel column label 'L' to '12'
    converts Excel column label 'Z' to '26'
    converts Excel column label 'AA' to '27'
    converts Excel column label 'AB' to '28'
    converts Excel column label 'AC' to '29'
    converts Excel column label 'AAA' to '53'
    converts Excel column label 'AAB' to '54'
    converts Excel column label 'AAC' to '55'
    converts Excel column label 'AAAA' to '79'
    converts Excel column label 'AAAB' to '80'
    converts Excel column label 'AAAC' to '81'
  #complex_excel_to_decimal
    converts Excel column label 'A' to '1'
    converts Excel column label 'B' to '2'
    converts Excel column label 'L' to '12'
    converts Excel column label 'Y' to '25'
    converts Excel column label 'Z' to '26'
    converts Excel column label 'AA' to '27'
    converts Excel column label 'AB' to '28'
    converts Excel column label 'BA' to '53'
    converts Excel column label 'BB' to '54'
    converts Excel column label 'HQ' to '225'
    converts Excel column label 'MM' to '351'
    converts Excel column label 'SI' to '503'
    converts Excel column label 'ZZ' to '702'
    converts Excel column label 'AAA' to '703'
    converts Excel column label 'AAB' to '704'
    converts Excel column label 'ABA' to '729'
    converts Excel column label 'ABB' to '730'
    converts Excel column label 'DJW' to '2987'
    converts Excel column label 'ZZZ' to '18278'
    converts Excel column label 'AAAA' to '18279'
    converts Excel column label 'AAAB' to '18280'
    converts Excel column label 'AABA' to '18305'
    converts Excel column label 'AABB' to '18306'
    converts Excel column label 'ABAA' to '18955'
    converts Excel column label 'ABAB' to '18956'
    converts Excel column label 'COWN' to '63480'
    converts Excel column label 'IHQU' to '164055'
    converts Excel column label 'ZZZZ' to '475254'
  #decimal_to_complex_excel
    converts '1' to Excel column label 'A'
    converts '2' to Excel column label 'B'
    converts '12' to Excel column label 'L'
    converts '25' to Excel column label 'Y'
    converts '26' to Excel column label 'Z' (FAILED - 1)
    converts '27' to Excel column label 'AA'
    converts '28' to Excel column label 'AB'
    converts '53' to Excel column label 'BA'
    converts '54' to Excel column label 'BB'
    converts '225' to Excel column label 'HQ'
    converts '351' to Excel column label 'MM'
    converts '503' to Excel column label 'SI'
    converts '702' to Excel column label 'ZZ' (FAILED - 2)
    converts '703' to Excel column label 'AAA'
    converts '704' to Excel column label 'AAB'
    converts '729' to Excel column label 'ABA'
    converts '730' to Excel column label 'ABB'
    converts '2987' to Excel column label 'DJW'
    converts '18278' to Excel column label 'ZZZ' (FAILED - 3)
    converts '18279' to Excel column label 'AAAA'
    converts '18280' to Excel column label 'AAAB'
    converts '18305' to Excel column label 'AABA'
    converts '18306' to Excel column label 'AABB'
    converts '18955' to Excel column label 'ABAA'
    converts '18956' to Excel column label 'ABAB'
    converts '63480' to Excel column label 'COWN'
    converts '164055' to Excel column label 'IHQU'
    converts '475254' to Excel column label 'ZZZZ' (FAILED - 4)

Failures:

  1) ExcelAlpha#decimal_to_complex_excel converts '26' to Excel column label 'Z'
     Failure/Error: expect(described_class.decimal_to_complex_excel(int)).to eq(col)

       expected: "Z"
            got: "A"

       (compared using ==)
     # ./excel_alpha.rb:125:in `block (4 levels) in <top (required)>'

  2) ExcelAlpha#decimal_to_complex_excel converts '702' to Excel column label 'ZZ'
     Failure/Error: expect(described_class.decimal_to_complex_excel(int)).to eq(col)

       expected: "ZZ"
            got: "AA"

       (compared using ==)
     # ./excel_alpha.rb:125:in `block (4 levels) in <top (required)>'

  3) ExcelAlpha#decimal_to_complex_excel converts '18278' to Excel column label 'ZZZ'
     Failure/Error: expect(described_class.decimal_to_complex_excel(int)).to eq(col)

       expected: "ZZZ"
            got: "AAA"

       (compared using ==)
     # ./excel_alpha.rb:125:in `block (4 levels) in <top (required)>'

  4) ExcelAlpha#decimal_to_complex_excel converts '475254' to Excel column label 'ZZZZ'
     Failure/Error: expect(described_class.decimal_to_complex_excel(int)).to eq(col)

       expected: "ZZZZ"
            got: "AAAA"

       (compared using ==)
     # ./excel_alpha.rb:125:in `block (4 levels) in <top (required)>'

Finished in 0.028 seconds (files took 0.15644 seconds to load)
70 examples, 4 failures

Failed examples:

rspec ./excel_alpha.rb[1:4:5] # ExcelAlpha#decimal_to_complex_excel converts '26' to Excel column label 'Z'
rspec ./excel_alpha.rb[1:4:13] # ExcelAlpha#decimal_to_complex_excel converts '702' to Excel column label 'ZZ'
rspec ./excel_alpha.rb[1:4:19] # ExcelAlpha#decimal_to_complex_excel converts '18278' to Excel column label 'ZZZ'
rspec ./excel_alpha.rb[1:4:28] # ExcelAlpha#decimal_to_complex_excel converts '475254' to Excel column label 'ZZZZ'

more difficult version of inputs and outputs would be rotating through the alphabet in the first column before adding a letter to the end, to an infinite length

its basically hex calculator but base 26

and the inverse would pretty complex as well, go from decimal to excel representation

updated for base26 calculation.