Excel column numbers to letters

[chawki...@gmail.com]

Hi everybody.

I'm trying to write a procedure to convert a column number (integer) to an Excel column name. I think I'm almost there but the procedure degenerates when the column names go to multiple letters (AA, BA, etc.).

Here's what I have so far:

proc _columnNumberToLetter {columnNumber} {
set listLetters [list A B C D E F G H I J K L M N O P Q R S T U V W X Y Z]
if {$columnNumber <= 26} {
set columnLetter [lindex $listLetters [expr $columnNumber - 1]]
} else {
set tickOver [expr int($columnNumber / 26)]
set remainder [expr $columnNumber - ($tickOver * 26)]
if {$tickOver <= 26} {
append columnLetter [lindex $listLetters [expr $tickOver - 1]]
if {$remainder == 0} {
append columnLetter "Z"
} else {
append columnLetter [lindex $listLetters [expr $remainder - 1]]
}
}
}
return $columnLetter
}

As I said, it's 'mostly' working. :) So if I pass the procedure a value of 1, it'll return 'A'. A value of 26 returns 'Z'. However, a value of 52 returns 'BZ' when it should be 'AZ'.

I'm sure I'm missing something obvious. I seem to think that I'll need to recurse the procedure to handle all possible numbers. I'd greatly appreciate anybody's help.

CJ

[Andreas Leitgeb]

chawki...@gmail.com <chawki...@gmail.com> wrote:
> Here's what I have so far:
> proc _columnNumberToLetter {columnNumber} {

We had this topic here just recently. This was my final answer:
http://groups.google.com/group/comp.lang.tcl/msg/7dda41fdc277ec6b
or search for
Message-Id: slrnjrs37...@gamma.logic.tuwien.ac.at

[Gerald W. Lester]

Makes one wonder this is homework for some course.

Also makes one wonder if students ever think that the instructor may also be
reading c.l.t.

--
+------------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald...@kng-consulting.net |
+------------------------------------------------------------------------+

[Andreas Leitgeb]

Gerald W. Lester <Gerald...@KnG-Consulting.net> wrote:
> On 5/30/12 3:30 PM, Andreas Leitgeb wrote:
>> chawki...@gmail.com<chawki...@gmail.com> wrote:
>>> Here's what I have so far:
>>> proc _columnNumberToLetter {columnNumber} {
>>
>> We had this topic here just recently. This was my final answer:
>> http://groups.google.com/group/comp.lang.tcl/msg/7dda41fdc277ec6b
>> or search for
>> Message-Id: slrnjrs37...@gamma.logic.tuwien.ac.at
> Makes one wonder this is homework for some course.

At least in the referenced case, I was pretty sure that
it wasn't, as it emerged from a different problem.

This time, I'm not sure, but still confident, that it wasn't.
My "homework assignment detector" just didn't ring and I know
how it sounds when it does. We may see, if it requires some
recalibration, though ;-)

[Uwe Klein]

If I have understood the objective properly:

columns start @ 1
#!/usr/bin/tclsh

set config(atags) {A B C D E F G H I J K L M N O P Q R S T U V W X Y Z}
set config(alen) [ llength $config(atags) ]

#debug:
puts stderr "config(atags) $config(atags)"
puts stderr "config(alen) $config(alen)"

proc num2col num {
incr num -1
return [ num2col_helper $num ]
}
proc num2col_helper num {
upvar ::config c
set blk [expr { $num / $c(alen) } ]
set rem [expr { $num % $c(alen) } ]
if {$blk} {
append ret [ num2col $blk ]
}
append ret [ lindex $c(atags) $rem ]
return $ret
}

# test upto 3 letters:
set num 1
while {$num < 800} {
puts [format "num: % 8d col: % 10s" $num [ num2col $num ] ]
incr num
}
# end

uwe

[CJ]

Hello Uwe.

Thanks very much. Exactly what I needed; works like charm. An no
this is not for homework. I'm taking tabular data and spitting out
from one piece of software to Excel. I needed to be able to address
the column names based on a previously-derived column number.

Thanks again,
CHJ