Topic: Excel Column Row cell ID to number list

[terrycadd]

Here's the associated function that references Number2Column. 
I want it to default to "A1" if there's a problem. 
My version is too limited, as the range only covers "A1" through "ZZ###...". 
It should be able to cover any Excel column value, i.e. "ZYXWV..." 
If any of you have a similar function or a better resolution please submit it.
Thanks for your assistance.
Terry

Code: [Select]

; Column_Row - Returns a list of the Column and Row number
; Syntax example: (Column_Row "ZZ999") = '(702 999)
(defun Column_Row (Cell$ / Chr1$ Chr2$ Column# Row#)
  (if (< (strlen Cell$) 2)(setq Cell$ "A1"))
  (setq Cell$ (strcase Cell$))
  (setq Chr1$ (substr Cell$ 1 1))
  (setq Chr2$ (substr Cell$ 2 1))
  (cond
    ((and (>= (ascii Chr1$) 65)(<= (ascii Chr1$) 90)(>= (ascii Chr2$) 65)(<= (ascii Chr2$) 90))
      (setq Column# (+ (* (- (ascii Chr1$) 64) 26)(- (ascii Chr2$) 64)))
      (setq Row# (atoi (substr Cell$ 3)))
    );case
    ((and (>= (ascii Chr1$) 65)(<= (ascii Chr1$) 90))
      (setq Column# (- (ascii Chr1$) 64))
      (setq Row# (atoi (substr Cell$ 2)))
    );case
    (t (setq Column# 1 Row# 1))
  );cond
  ;default to "A1" if there's a problem
  (if (< Column# 1) (setq Column# 1))
  (if (< Row# 1) (setq Row# 1))
  (list Column# Row#)
);defun Column_Row
Why am I still a Newt? Can't I be a Bull Frog instead?

[gile]

Something like this ?
With the second routine (alpha2num) I gave in the other thread.

Code: [Select]

(defun Column-Row (cell / col char row)
  (setq col "")
  (while (< 64 (ascii (setq char (strcase (substr cell 1 1)))) 91)
    (setq col  (strcat col char)
  cell (substr cell 2)
    )
  )
  (if (and (/= col "") (numberp (setq row (read cell))))
    (list (alpha2num col) row)
    '(1 1) ;default to "A1" if there's a problem
  )
)


(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))
    )
  )
)

[terrycadd]

Yes, excellent! 

Your version:
Command: (column-row "ZZZ999") = (18278 999) correct!!!
My version:
Command: (column_row "ZZZ999") = (702 1) wrong answer...

[Jakes GrandPa]

Why am I still a Newt? Can't I be a Bull Frog instead?

Yes, as soon as you get 201 posts. I'm working on it too.

[Kerry]

Newts can take several years to mature. If you are prepared to share the music you're listening to and tell jokes and join in political and religious discussions you'll be a croaker before you know it.

.... but that may not help with your understanding of code or CAD.




qualified:
despite some opinions to the contrary, post count reflects a magnitude of quantity, not necessarily characterising quality .

[terrycadd]

Ok, if I can’t be a Bull Frog, how about a Tadpole instead?  (201 posts!)  I have a day job programming 8 to 5 with my boss near by.  How am I going to achieve 201 posts any time soon?  That’s kinda’ high, don’t you think?  A hundred maybe…  but 201?  I can’t just engage in idle chat of three word sentences, just to get up to 201 posts.
I want to be a Bull Frog!  (ri’vet! ri’vet!)   :whistle:
Terry Cadd

[Krushert]

qualified:
despite some opinions to the contrary, post count reflects a magnitude of quantity, not necessarily characterising quality .

What Kerry said is that we talk too much but choose to use big words in saying so.