Jump to content

change numbers <-> alphabet column numbers

exceed

By exceed
in AutoLISP, Visual LISP & DCL

 Share

Recommended Posts

exceed Proficient

exceed

Posted
Posted 
; EX:NUM->COL - 2022.06.17 exceed
; change numbers to alphabet numbers. (like EXCEL column)
; example ) A, B, C ~ Y, Z, AA ZZ, AAA ~ ZZZ, AAAA ~ ZZZZ, AAAAA ~ ZZZZZ
; allowed numbers 1 to 12356630
;  
; How to use
; (EX:NUM->COL number)


(defun EX:NUM->COL ( c / answer c1 c2 c3 c4 c5 )
  (setq c (- c 1))
  (cond
    ((< c 0) ; case for under 0 value
      (setq answer (strcat "out of range"))
    )
    ((and (>= c 0) (< c 26)) ; case for 1 ~ 26 (= A ~ Z)
      (setq c1 (+ c 1))
      (setq answer (strcat (chr (+ 64 c1))))
    )
    ((and (>= c 26) (< c (+ 26 (* 26 26))) ) ; case for 26 ~ 702 (= AA ~ ZZ)
      (setq c (- c 26))
      (setq c2 (fix (/ c 26)))
      (setq c1 (- c (* c2 26)))
      (setq c1 (+ c1 1))
      (setq c2 (+ c2 1))
      (setq answer (strcat (chr (+ 64 c2)) (chr (+ 64 c1))))
    )
    ((and (>= c (+ 26 (* 26 26))) (< c (+ 26 (* 26 26) (* 26 26 26)))) ; case for 702 ~ 18278 (= AAA ~ ZZZ)
      (setq c (- c (+ 26 (* 26 26))))
      (setq c3 (fix (/ c (* 26 26)) ) )
      (setq c2 (fix (/ (- c (* c3 26 26)) 26)))
      (setq c1 (- (- c (* c3 26 26)) (* c2 26)))
      (setq c1 (+ c1 1))
      (setq c2 (+ c2 1))
      (setq c3 (+ c3 1))
      (setq answer (strcat (chr (+ 64 c3)) (chr (+ 64 c2)) (chr (+ 64 c1))))
    )
    ((and (>= c (+ 26 (* 26 26) (* 26 26 26))) (< c (+ 26 (* 26 26) (* 26 26 26) (* 26 26 26 26)) )) ; case for 18278 ~ 475254 (= AAAA
      (setq c (- c (+ 26 (* 26 26) (* 26 26 26))))
      (setq c4 (fix (/ c (* 26 26 26))))
      (setq c3 (fix (/ (- c (* c4 26 26 26)) (* 26 26))))
      (setq c2 (fix (/ (- (- c (* c4 26 26 26)) (* c3 26 26)) 26)))
      (setq c1 (- (- (- c (* c4 26 26 26)) (* c3 26 26)) (* c2 26)))
      (setq c1 (+ c1 1))
      (setq c2 (+ c2 1))
      (setq c3 (+ c3 1))
      (setq c4 (+ c4 1))
      (setq answer (strcat (chr (+ 64 c4)) (chr (+ 64 c3)) (chr (+ 64 c2)) (chr (+ 64 c1))))
    )
    ((and (>= c (+ 26 (* 26 26) (* 26 26 26) (* 26 26 26 26))) (< c (+ 26 (* 26 26) (* 26 26 26) (* 26 26 26 26) (* 26 26 26 26 26)))) ; case for 475254 ~ 12356630
      (setq c (- c (+ 26 (* 26 26) (* 26 26 26) (* 26 26 26 26))))
      (setq c5 (fix (/ c (* 26 26 26 26))))
      (setq c4 (fix (/ (- c (* c5 26 26 26 26)) (* 26 26 26))))
      (setq c3 (fix (/ (- (- c (* c5 26 26 26 26)) (* c4 26 26 26)) (* 26 26))))
      (setq c2 (fix (/ (- (- (- c (* c5 26 26 26 26)) (* c4 26 26 26)) (* c3 26 26)) 26)))
      (setq c1 (- (- (- (- c (* c5 26 26 26 26)) (* c4 26 26 26)) (* c3 26 26)) (* c2 26)))
      (setq c1 (+ c1 1))
      (setq c2 (+ c2 1))
      (setq c3 (+ c3 1))
      (setq c4 (+ c4 1))
      (setq c5 (+ c5 1))
      (setq answer (strcat (chr (+ 64 c5)) (chr (+ 64 c4)) (chr (+ 64 c3)) (chr (+ 64 c2)) (chr (+ 64 c1))))
    )
    ((>= c (+ 26 (* 26 26) (* 26 26 26) (* 26 26 26 26) (* 26 26 26 26 26))) ; case for over 12356630
      (setq answer (strcat "out of range"))
    )
  )
  answer
)

; EX:COL->NUM - 2022.06.17 exceed
; reverse function of EX:NUM->COL
; allowed alphabets A to ZZZZZ
;  
; How to use
; (EX:COL->NUM string)

(defun EX:COL->NUM ( c / answer clen cflag clist index catom clistlen )
  (setq c (strcase c))
  (setq clen (strlen c))
  (if (and (> clen 0) (< clen 6))
    (progn
      (setq cflag 0)
      (setq clist '())
      (setq index 1)
      (repeat clen
        (setq catom (ascii (substr c index 1)))
        (if (and (>= catom 65) (<= catom 90))
          (setq clist (cons (- catom 64) clist))
          (setq cflag 1)
        )
        (setq index (+ index 1))
      )
      (if (= cflag 0)
        (progn
          (setq clist (reverse clist))
          (setq clistlen (length clist))
          (cond 
            ((= clistlen 1)
              (setq answer (nth 0 clist))
            )
            ((= clistlen 2)
              (setq answer (+ (* (nth 0 clist) 26) (nth 1 clist)))
            )
            ((= clistlen 3)
              (setq answer (+ (* (nth 0 clist) 26 26) (* (nth 1 clist) 26) (nth 2 clist)))
            )
            ((= clistlen 4)
              (setq answer (+ (* (nth 0 clist) 26 26 26) (* (nth 1 clist) 26 26) (* (nth 2 clist) 26) (nth 3 clist)))
            )
            ((= clistlen 5)
              (setq answer (+ (* (nth 0 clist) 26 26 26 26) (* (nth 1 clist) 26 26 26) (* (nth 2 clist) 26 26) (* (nth 3 clist) 26) (nth 4 clist)))
            )
          )
        )
        (progn
          (setq answer "Non-alphabetic impurities are mixed.")
        )
      )
    )
    (progn
      (setq answer "Only 1 ~ 5 digits Alphabet can be calculated.")
    )
  )
  answer    
)

 

 

i have organized parts of this code for use elsewhere.

https://www.cadtutor.net/forum/topic/74681-cad-output-excel-specified-page/?do=findComment&comment=591539

 

 

It seems like it could be made more concisely, but at my level, this is the limit.

 

(EX:NUM->COL number)

(EX:COL->NUM string)

 

test code is below 

(defun c:test ( / a )
  (princ "\n ================== ")
  (setq a 1)
  (repeat 40
    (princ "\n input = ")
    (princ a)
    (princ " / NUM->COL = ")
    (princ (EX:NUM->COL a))
    (princ " / COL->NUM = ")
    (princ (EX:COL->NUM (EX:NUM->COL a)))
    (setq a (+ a 1))
  )
  (princ "\n ================== ")
  (setq a 690)
  (repeat 20
    (princ "\n input = ")
    (princ a)
    (princ " / NUM->COL = ")
    (princ (EX:NUM->COL a))
    (princ " / COL->NUM = ")
    (princ (EX:COL->NUM (EX:NUM->COL a)))
    (setq a (+ a 1))
  )
  (princ "\n ================== ")
  (setq a 18270)
  (repeat 20
    (princ "\n input = ")
    (princ a)
    (princ " / NUM->COL = ")
    (princ (EX:NUM->COL a))
    (princ " / COL->NUM = ")
    (princ (EX:COL->NUM (EX:NUM->COL a)))
    (setq a (+ a 1))
  )
  (princ "\n ================== ")
  (setq a 475240)
  (repeat 20
    (princ "\n input = ")
    (princ a)
    (princ " / NUM->COL = ")
    (princ (EX:NUM->COL a))
    (princ " / COL->NUM = ")
    (princ (EX:COL->NUM (EX:NUM->COL a)))
    (setq a (+ a 1))
  )
  (princ "\n ================== ")
  (setq a 12356625)
  (repeat 10
    (princ "\n input = ")
    (princ a)
    (princ " / NUM->COL = ")
    (princ (EX:NUM->COL a))
    (princ " / COL->NUM = ")
    (princ (EX:COL->NUM (EX:NUM->COL a)))
    (setq a (+ a 1))
  )
  (princ "\n ================== ")
  (princ)
)

 

and test result is below 

 ================== 
 input = 1 / NUM->COL = A / COL->NUM = 1
 input = 2 / NUM->COL = B / COL->NUM = 2
 input = 3 / NUM->COL = C / COL->NUM = 3
 input = 4 / NUM->COL = D / COL->NUM = 4
 input = 5 / NUM->COL = E / COL->NUM = 5
 input = 6 / NUM->COL = F / COL->NUM = 6
 input = 7 / NUM->COL = G / COL->NUM = 7
 input = 8 / NUM->COL = H / COL->NUM = 8
 input = 9 / NUM->COL = I / COL->NUM = 9
 input = 10 / NUM->COL = J / COL->NUM = 10
 input = 11 / NUM->COL = K / COL->NUM = 11
 input = 12 / NUM->COL = L / COL->NUM = 12
 input = 13 / NUM->COL = M / COL->NUM = 13
 input = 14 / NUM->COL = N / COL->NUM = 14
 input = 15 / NUM->COL = O / COL->NUM = 15
 input = 16 / NUM->COL = P / COL->NUM = 16
 input = 17 / NUM->COL = Q / COL->NUM = 17
 input = 18 / NUM->COL = R / COL->NUM = 18
 input = 19 / NUM->COL = S / COL->NUM = 19
 input = 20 / NUM->COL = T / COL->NUM = 20
 input = 21 / NUM->COL = U / COL->NUM = 21
 input = 22 / NUM->COL = V / COL->NUM = 22
 input = 23 / NUM->COL = W / COL->NUM = 23
 input = 24 / NUM->COL = X / COL->NUM = 24
 input = 25 / NUM->COL = Y / COL->NUM = 25
 input = 26 / NUM->COL = Z / COL->NUM = 26
 input = 27 / NUM->COL = AA / COL->NUM = 27
 input = 28 / NUM->COL = AB / COL->NUM = 28
 input = 29 / NUM->COL = AC / COL->NUM = 29
 input = 30 / NUM->COL = AD / COL->NUM = 30
 input = 31 / NUM->COL = AE / COL->NUM = 31
 input = 32 / NUM->COL = AF / COL->NUM = 32
 input = 33 / NUM->COL = AG / COL->NUM = 33
 input = 34 / NUM->COL = AH / COL->NUM = 34
 input = 35 / NUM->COL = AI / COL->NUM = 35
 input = 36 / NUM->COL = AJ / COL->NUM = 36
 input = 37 / NUM->COL = AK / COL->NUM = 37
 input = 38 / NUM->COL = AL / COL->NUM = 38
 input = 39 / NUM->COL = AM / COL->NUM = 39
 input = 40 / NUM->COL = AN / COL->NUM = 40
 ================== 
 input = 690 / NUM->COL = ZN / COL->NUM = 690
 input = 691 / NUM->COL = ZO / COL->NUM = 691
 input = 692 / NUM->COL = ZP / COL->NUM = 692
 input = 693 / NUM->COL = ZQ / COL->NUM = 693
 input = 694 / NUM->COL = ZR / COL->NUM = 694
 input = 695 / NUM->COL = ZS / COL->NUM = 695
 input = 696 / NUM->COL = ZT / COL->NUM = 696
 input = 697 / NUM->COL = ZU / COL->NUM = 697
 input = 698 / NUM->COL = ZV / COL->NUM = 698
 input = 699 / NUM->COL = ZW / COL->NUM = 699
 input = 700 / NUM->COL = ZX / COL->NUM = 700
 input = 701 / NUM->COL = ZY / COL->NUM = 701
 input = 702 / NUM->COL = ZZ / COL->NUM = 702
 input = 703 / NUM->COL = AAA / COL->NUM = 703
 input = 704 / NUM->COL = AAB / COL->NUM = 704
 input = 705 / NUM->COL = AAC / COL->NUM = 705
 input = 706 / NUM->COL = AAD / COL->NUM = 706
 input = 707 / NUM->COL = AAE / COL->NUM = 707
 input = 708 / NUM->COL = AAF / COL->NUM = 708
 input = 709 / NUM->COL = AAG / COL->NUM = 709
 ================== 
 input = 18270 / NUM->COL = ZZR / COL->NUM = 18270
 input = 18271 / NUM->COL = ZZS / COL->NUM = 18271
 input = 18272 / NUM->COL = ZZT / COL->NUM = 18272
 input = 18273 / NUM->COL = ZZU / COL->NUM = 18273
 input = 18274 / NUM->COL = ZZV / COL->NUM = 18274
 input = 18275 / NUM->COL = ZZW / COL->NUM = 18275
 input = 18276 / NUM->COL = ZZX / COL->NUM = 18276
 input = 18277 / NUM->COL = ZZY / COL->NUM = 18277
 input = 18278 / NUM->COL = ZZZ / COL->NUM = 18278
 input = 18279 / NUM->COL = AAAA / COL->NUM = 18279
 input = 18280 / NUM->COL = AAAB / COL->NUM = 18280
 input = 18281 / NUM->COL = AAAC / COL->NUM = 18281
 input = 18282 / NUM->COL = AAAD / COL->NUM = 18282
 input = 18283 / NUM->COL = AAAE / COL->NUM = 18283
 input = 18284 / NUM->COL = AAAF / COL->NUM = 18284
 input = 18285 / NUM->COL = AAAG / COL->NUM = 18285
 input = 18286 / NUM->COL = AAAH / COL->NUM = 18286
 input = 18287 / NUM->COL = AAAI / COL->NUM = 18287
 input = 18288 / NUM->COL = AAAJ / COL->NUM = 18288
 input = 18289 / NUM->COL = AAAK / COL->NUM = 18289
 ================== 
 input = 475240 / NUM->COL = ZZZL / COL->NUM = 475240
 input = 475241 / NUM->COL = ZZZM / COL->NUM = 475241
 input = 475242 / NUM->COL = ZZZN / COL->NUM = 475242
 input = 475243 / NUM->COL = ZZZO / COL->NUM = 475243
 input = 475244 / NUM->COL = ZZZP / COL->NUM = 475244
 input = 475245 / NUM->COL = ZZZQ / COL->NUM = 475245
 input = 475246 / NUM->COL = ZZZR / COL->NUM = 475246
 input = 475247 / NUM->COL = ZZZS / COL->NUM = 475247
 input = 475248 / NUM->COL = ZZZT / COL->NUM = 475248
 input = 475249 / NUM->COL = ZZZU / COL->NUM = 475249
 input = 475250 / NUM->COL = ZZZV / COL->NUM = 475250
 input = 475251 / NUM->COL = ZZZW / COL->NUM = 475251
 input = 475252 / NUM->COL = ZZZX / COL->NUM = 475252
 input = 475253 / NUM->COL = ZZZY / COL->NUM = 475253
 input = 475254 / NUM->COL = ZZZZ / COL->NUM = 475254
 input = 475255 / NUM->COL = AAAAA / COL->NUM = 475255
 input = 475256 / NUM->COL = AAAAB / COL->NUM = 475256
 input = 475257 / NUM->COL = AAAAC / COL->NUM = 475257
 input = 475258 / NUM->COL = AAAAD / COL->NUM = 475258
 input = 475259 / NUM->COL = AAAAE / COL->NUM = 475259
 ================== 
 input = 12356625 / NUM->COL = ZZZZU / COL->NUM = 12356625
 input = 12356626 / NUM->COL = ZZZZV / COL->NUM = 12356626
 input = 12356627 / NUM->COL = ZZZZW / COL->NUM = 12356627
 input = 12356628 / NUM->COL = ZZZZX / COL->NUM = 12356628
 input = 12356629 / NUM->COL = ZZZZY / COL->NUM = 12356629
 input = 12356630 / NUM->COL = ZZZZZ / COL->NUM = 12356630
 input = 12356631 / NUM->COL = out of range / COL->NUM = Only 1 ~ 5 digits Alphabet can be calculated.
 input = 12356632 / NUM->COL = out of range / COL->NUM = Only 1 ~ 5 digits Alphabet can be calculated.
 input = 12356633 / NUM->COL = out of range / COL->NUM = Only 1 ~ 5 digits Alphabet can be calculated.
 input = 12356634 / NUM->COL = out of range / COL->NUM = Only 1 ~ 5 digits Alphabet can be calculated.
 ==================

 

 

 

I've been wrong on this problem twice before, sadly

so please let me know if you find anything wrong number in 1 ~ 12356630

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...