CODEWITHSUNDEEP
rubystringdata-conversion
gorn
gorn

Reputation: 5340

How do I convert a spreadsheet "letternamed" column coordinate to an integer?

In spreadsheets I have cells named like "F14", "BE5" or "ALL1". I have the first part, the column coordinate, in a variable and I want to convert it to a 0-based integer column index.

How do I do it, preferably in an elegant way, in Ruby?

I can do it using a brute-force method: I can imagine loopping through all letters, converting them to ASCII and adding to a result, but I feel there should be something more elegant/straightforward.

Edit: Example: To simplify I do only speak about the column coordinate (letters). Therefore in the first case (F14) I have "F" as the input and I expect the result to be 5. In the second case I have "BE" as input and I expect getting 56, for "ALL" I want to get 999.

Upvotes: 3

Views: 222

Answers (3)

gorn
gorn

Reputation: 5340

I have found particularly neat way to do this conversion:

def index_from_column_name(colname)
  s=colname.size
  (colname.to_i(36)-(36**s-1).div(3.5)).to_s(36).to_i(26)+(26**s-1)/25-1
end

Explanation why it works

(warning spoiler ;) ahead). Basically we are doing this

(colname.to_i(36)-('A'*colname.size).to_i(36)).to_s(36).to_i(26)+('1'*colname.size).to_i(26)-1

which in plain English means, that we are interpreting colname as 26-base number. Before we can do it we need to interpret all A's as 1, B's as 2 etc. If only this is needed than it would be even simpler, namely 

(colname.to_i(36) - '9'*colname.size).to_i(36)).to_s(36).to_i(26)-1

unfortunately there are Z characters present which would need to be interpreted as 10(base 26) so we need a little trick. We shift every digit 1 more then needed and than add it at the end (to every digit in original colname) `

Upvotes: 0

wicz
wicz

Reputation: 2313

We can use modulo and the length of the input. The last character will be used to calculate the exact "position", and the remainders to count how many "laps" we did in the alphabet, e.g.

def column_to_integer(column_name)
  letters   = /[A-Z]+/.match(column_name).to_s.split("")
  laps      = (letters.length - 1) * 26
  position  = ((letters.last.ord - 'A'.ord) % 26)

  laps + position
end

Using decimal representation (ord) and the math tricks seems a neat solution at first, but it has some pain points regarding the implementation. We have magic numbers, 26, and constants 'A'.ord all over.

One solution is to give our code better knowlegde about our domain, i.e. the alphabet. In that case, we can switch the modulo with the position of the last character in the alphabet (because it's already sorted in a zero-based array), e.g.

ALPHABET = ('A'..'Z').to_a

def column_to_integer(column_name)
  letters   = /[A-Z]+/.match(column_name).to_s.split("")
  laps      = (letters.length - 1) * ALPHABET.size
  position  = ALPHABET.index(letters.last)

  laps + position
end

The final result:

> column_to_integer('F5')
=> 5
> column_to_integer('AK14')
=> 36

HTH. Best!

Upvotes: 1

Tim Destan
Tim Destan

Reputation: 2028

Not sure if this is any clearer than the code you already have, but it does have the advantage of handling an arbitrary number of letters:

class String
  def upcase_letters
    self.upcase.split(//)
  end
end

module Enumerable
  def reverse_with_index
    self.map.with_index.to_a.reverse
  end

  def sum
    self.reduce(0, :+)
  end
end

def indexFromColumnName(column_str)
  start = 'A'.ord - 1
  column_str.upcase_letters.map do |c| 
    c.ord - start
  end.reverse_with_index.map do |value, digit_position|
    value * (26 ** digit_position)
  end.sum - 1
end

I've added some methods to String and Enumerable because I thought it made the code more readable, but you could inline these or define them elsewhere if you don't like that sort of thing.

Upvotes: 3

Related Questions