Column Reference Functions
Introduction
Here are various subfunctions for manipulating alphabetical column references (e.g. A,B,...,Z,AA,...) as used by applications such as Excel.
Information about the purpose, arguments and returns of each function is detailed in each respective code header. An example demonstrating how to call the function with the correct arguments is also supplied.
Contents
Number to Column
Select all
;; Number to Column - Lee Mac ;; Converts an integer into a column reference, e.g. 27 -> AA ;; n - [int] positive non-zero integer (defun LM:num->col ( n ) (if (< n 27) (chr (+ 64 n)) (strcat (LM:num->col (/ (1- n) 26)) (LM:num->col (1+ (rem (1- n) 26)))) ) )
Example Function Call
_$ (LM:num->col 20) "T" _$ (LM:num->col 27) "AA" _$ (LM:num->col 731) "ABC"
Column to Number
Select all
;; Column to Number - Lee Mac ;; Converts a column reference into an integer, e.g. AA -> 27 ;; c - [str] upper-case string representing column reference (defun LM:col->num ( c / n ) (if (= 1 (setq n (strlen c))) (- (ascii c) 64) (+ (* 26 (LM:col->num (substr c 1 (1- n)))) (LM:col->num (substr c n))) ) )
Alternative Version
Select all
;; Column to Number - Lee Mac ;; Converts a column reference into an integer, e.g. AA -> 27 ;; c - [str] upper-case string representing column reference (defun LM:col->num ( c ) ( (lambda ( f ) (f (reverse (vl-string->list c)))) (lambda ( l ) (if l (+ (* 26 (f (cdr l))) (- (car l) 64)) 0)) ) )
Example Function Call
_$ (LM:col->num "T") 20 _$ (LM:col->num "AA") 27 _$ (LM:col->num "ABC") 731
Increment Alpha
Select all
;; Increment Alpha - Lee Mac ;; Increments an uppercase alphabetical string by a given number of units, e.g. AX + 4 = BB ;; a - [str] uppercase alphabetical string ;; i - [int] integer increment (defun LM:incalpha ( a i / c n ) (if (< 0 i) (if (zerop (setq n (strlen a))) (LM:incalpha "A" (1- i)) (if (= 90 (setq c (ascii (substr a n)))) (LM:incalpha (strcat (LM:incalpha (substr a 1 (1- n)) 1) "A") (1- i)) (strcat (LM:incalpha (substr a 1 (1- n)) (/ (+ i c -65) 26)) (chr (+ 65 (rem (+ i c -65) 26)))) ) ) a ) )
Alternative Version
Select all
;; Increment Alpha - Lee Mac ;; Increments an uppercase alphabetical string by a given number of units, e.g. AX + 4 = BB ;; a - [str] uppercase alphabetical string ;; i - [int] integer increment (defun LM:incalpha ( a i ) ( (lambda ( f ) (vl-list->string (reverse (f (reverse (vl-string->list a)) i)))) (lambda ( l i ) (if (< 0 i) (if (car l) (if (= 90 (car l)) (f (cons 65 (f (cdr l) 1)) (1- i)) (cons (+ 65 (rem (+ i (car l) -65) 26)) (f (cdr l) (/ (+ i (car l) -65) 26))) ) (f '(65) (1- i)) ) l ) ) ) )
Version Derived from Column Functions
Select all
;; Increment Alpha - Lee Mac ;; Increments an uppercase alphabetical string by a given number of units, e.g. AX + 4 = BB ;; a - [str] uppercase alphabetical string ;; i - [int] integer increment (defun LM:incalpha ( a i ) (LM:num->col (+ i (LM:col->num a))) )
Example Function Call
_$ (LM:incalpha "A" 5) "F" _$ (LM:incalpha "AX" 10) "BH" _$ (LM:incalpha "ABC" 1) "ABD"
Alpha++
[ As an alternative to (LM:incalpha a 1) ]
Select all
;; Alpha++ - Lee Mac ;; Increments an uppercase alphabetical string by one, e.g. AZ => BA ;; a - [str] uppercase alphabetical string (defun LM:alpha++ ( a / n ) (if (= "" a) "A" (if (= "Z" (substr a (setq n (strlen a)))) (strcat (LM:alpha++ (substr a 1 (1- n))) "A") (strcat (substr a 1 (1- n)) (chr (1+ (ascii (substr a n))))) ) ) )
Alternative Version
Select all
;; Alpha++ - Lee Mac ;; Increments an uppercase alphabetical string by one, e.g. AZ => BA ;; a - [str] uppercase alphabetical string (defun LM:alpha++ ( a ) ( (lambda ( f ) (vl-list->string (reverse (f (reverse (vl-string->list a)))))) (lambda ( l ) (if l (if (= 90 (car l)) (cons 65 (f (cdr l))) (cons (1+ (car l)) (cdr l)) ) '(65) ) ) ) )
Example Function Call
_$ (LM:alpha++ "A") "B" _$ (LM:alpha++ "AZ") "BA" _$ (LM:alpha++ "ZZ") "AAA"