Hi guys,
Anyone got a way to take a number and spit out the appropriate column name for excel?
Both of the examples below fail for number’s above 26 that are multiples of 26 ( (26 * x) mod 26 = 0, and there’s no zeroth index for lists in applescript, so you get an error). The funny thing is that if this were a lesser code, like C++, then there’d be no problem.
I’d like to see a working version of this code. It seems silly that having list indices from 1-number instead of from 0-(number - 1) shouldn’t keep this kind of base conversion from being possible.
I’d also be very happy with some sort of built in functionality for this sort of thing, like an excel command.
Thanks,
Tim
Anyways, here’s one I wrote (sloppy, I know):
on excelColumn(num)
set columnNum to num
set column_ to {}
set alphabet to {"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 degree to 0
set degreeTest to 0
set answer to ((columnNum - 1) div (26 ^ degreeTest))
repeat until answer = 0
set answer to ((columnNum - 1) div (26 ^ degreeTest))
if answer = 0 then
set degree to degreeTest - 1
end if
set degreeTest to degreeTest + 1
end repeat
if degree > 0 then
set end of column_ to item (columnNum div (26 ^ degree)) of alphabet
set i_ to degree
repeat until i_ < 2
set end of column_ to item ((columnNum div (26 ^ i_)) mod 26) of alphabet
set i_ to i_ - 1
end repeat
set end of column_ to item (columnNum mod (26)) of alphabet
else
set end of column_ to item (columnNum div (26 ^ degree)) of alphabet
end if
return column_ as text
end excelColumn
and here’s one from Trash Man (http://macscripter.net/profile.php?id=9395):
columnLetterBase26(26) --> "z"
columnLetterBase26(27) --> "aa"
columnLetterBase26(28) --> "ab"
columnLetterBase26(289) --> "kc"
on columnLetterBase26(aDecimal)
try
set aDecimal to aDecimal as integer
on error
error "The parameter given to convert decimals to base 26 wasn't a number." from aDecimal to integer
end try
if aDecimal < 1 then error "The parameter given is smaller than 1" from aDecimal to integer
local Base26
set Base26 to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
-- search for largest exponent
set exponent to 0
repeat while (26 * (26 ^ exponent)) < aDecimal
set exponent to exponent + 1
end repeat
-- fill string
set repr26 to {}
repeat while exponent ≥ 0
-- find biggest suitable
set baseNumber to 26
repeat while (baseNumber * (26 ^ exponent)) > aDecimal
set baseNumber to baseNumber - 1
end repeat
-- add letter
set end of repr26 to item baseNumber of Base26
-- addapt values
set aDecimal to aDecimal - (baseNumber * (26 ^ exponent))
set exponent to exponent - 1
end repeat
exponent
return (repr26 as string)
end columnLetterBase26
Thanks,
TIm
