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"

textsize

increase · reset · decrease

Designed & Created by Lee Mac © 2010