Author Topic: Get column letters from a number...  (Read 2395 times)

0 Members and 1 Guest are viewing this topic.

terrycadd

  • Guest
Get column letters from a number...
« on: August 09, 2007, 06:48:10 PM »
I'm back!  Been kinda' busy at work.  I'm working on a function relating to gathering Excel information, and here is one of the functions that I feel can be simplified by you all.  I'm not sure if I needed the RoundUp: function, but the way I wrote it makes the values come out correct.  So I left it in anyway.  Any re-writes welcome.   :kewl:
Thanks
Terry Cadd 

Code: [Select]
; Number2Column - Returns the Column letter from a number
; Syntax example: (Number2Column 133) = "EC"
(defun Number2Column (Number# / Number1# Number2# RoundUp:)
  ;RoundUp: - Rounds up to even integers
  (defun RoundUp: (RealNumber~)
    (if (/= (fix RealNumber~) RealNumber~)
      (1+ (fix RealNumber~))
      (fix RealNumber~)
    );if
  );defun RoundUp:
  (setq Number1# (1- (RoundUp: (/ Number# 26.0))))
  (setq Number2# (- Number# (* Number1# 26)))
  (if (> Number1# 0)
    (strcat (chr (+ Number1# 64))(chr (+ Number2# 64)))
    (chr (+ Number2# 64))
  );if
);defun Number2Column

Patrick_35

  • Bull Frog
  • Posts: 276
  • Rennes, France
Re: Get column letters from a number...
« Reply #1 on: August 10, 2007, 07:10:37 AM »
Hi,
try

Code: [Select]
(defun Number2Column (nb)
  (if (> nb 26)
    (strcat (chr (+ 64 (fix (/ nb 26)))) (chr (+ 64 (rem nb 26))))
    (chr (+ 64 nb))
  )
)

(Number2Column 133) --> "EC"
(Number2Column 2)  --> "B"

@+
« Last Edit: August 10, 2007, 07:19:26 AM by Patrick_35 »
The shape even of the pyramids of Egypt shows that already the workmen tended to make some less and less.
Will Cuppy, 1884-1949.

gile

  • Water Moccasin
  • Posts: 2261
  • Marseille, France
Re: Get column letters from a number...
« Reply #2 on: August 10, 2007, 07:13:36 AM »
Hi,
Terry,
Your code works fine until 702 which returns "ZZ", but 703 returns "[A" instead of "AAA".

Patrick,
Your code works fine until 51 which returns "AY", but 52 returns "B@" instead of "AZ".

Here's another way, using a recursive form (assuming num is a strictely positive integer) :

Code: [Select]
(defun num2alpha (num / r)
  (if (< num 27)
    (chr (+ 64 num))
    (if (= 0 (setq r (rem num 26)))
      (strcat (num2alpha (1- (/ num 26))) "Z")
      (strcat (num2alpha (/ num 26)) (chr (+ 64 r)))
    )
  )
)

(num2alpha 702) -> "ZZ"
(num2alpha 703) -> "AAA"
(num2alpha (* 26 703)) -> "ZZZ"
(num2alpha (1+ (* 26 703))) -> "AAAA"
« Last Edit: August 10, 2007, 07:20:29 AM by gile »
Speaking English as a French Frog

Patrick_35

  • Bull Frog
  • Posts: 276
  • Rennes, France
Re: Get column letters from a number...
« Reply #3 on: August 10, 2007, 07:23:50 AM »
Comme le monde est petit  ^-^   

Hi (gile)

Your lisp is good, except that the columns of Excel go only up to IV, that is to say (Number2Column 256)

@+
The shape even of the pyramids of Egypt shows that already the workmen tended to make some less and less.
Will Cuppy, 1884-1949.

Patrick_35

  • Bull Frog
  • Posts: 276
  • Rennes, France
Re: Get column letters from a number...
« Reply #4 on: August 10, 2007, 07:38:51 AM »
Quote
but 52 returns "B@" instead of "AZ".
Exactly

Code: [Select]
(defun Number2Column (nb / i)
  (setq i (rem nb 26))
  (if (> nb 26)
    (if (zerop i)
      (strcat (chr (+ 63 (/ nb 26))) "Z")
      (strcat (chr (+ 64 (fix (/ nb 26)))) (chr (+ 64 i)))
    )
    (chr (+ 64 nb))
  )
)

(Number2Column 52) --> "AZ"
The shape even of the pyramids of Egypt shows that already the workmen tended to make some less and less.
Will Cuppy, 1884-1949.

gile

  • Water Moccasin
  • Posts: 2261
  • Marseille, France
Re: Get column letters from a number...
« Reply #5 on: August 10, 2007, 08:27:22 AM »
Quote
Your lisp is good, except that the columns of Excel go only up to IV, that is to say (Number2Column 256)

Exact, but I've done something more general (it's called "num2alpha" rather than "Number2Column"  :wink:), and if some new Excel release allows 1024 columns or more, I'm ready  :-D

Just for the fun, the reverse function

Code: [Select]
(defun alpha2num (str / n)
  (if (= 0 (setq n (strlen str)))
    0
    (+ (* (- (ascii (strcase (substr str 1 1))) 64)
  (expt 26 (1- n))
       )
       (alpha2num (substr str 2))
    )
  )
)
« Last Edit: August 10, 2007, 10:06:39 AM by gile »
Speaking English as a French Frog

terrycadd

  • Guest
Re: Get column letters from a number...
« Reply #6 on: August 10, 2007, 10:29:51 AM »
My version that I worked on yesterday was very limited to handle only the range from "A" to "ZZ".  The problem is sorta' like converting a number into a hexadecimal number base 16, only this equation is base 26.
I haven't tested the submissions above yet this morning, but I can tell right off that you guys are right on track.
Thanks
Terry