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

0 Members and 1 Guest are viewing this topic.

• 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

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"  ), and if some new Excel release allows 1024 columns or more, I'm ready

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