How to convert decimals to column names (spreadsheet app)?

Hi everybody,

I am currently working on a script, which will help our staff to automate tasks in a spreadsheet app (RagTime). Now I need a function for my script, which will return the column name for a given position. Here is an example, just to give you an idea about my requirements:

Position: 5 → Column name: “E”
Position: 28 → Column name: “AB”
Position 127 → Column name: “FL”

Well, I already started to code such a function, but unfortunately when I enter multiples of 26 I am running into problems. For example, the script returns the column name “A” for 26 and “B” for 52…


my get_column_name(125)

on get_column_name(position)
	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 column_name to ""
	
	set x to position
	repeat
		set xdiv to x div 26
		set xmod to x mod 26
		log xdiv & " | " & xmod
		
		if xmod > 0 then
			set column_name to (item xmod of alphabet) & column_name
		end if
		
		if x > 0 then
			set x to xdiv
		else if x = 0 then
			exit repeat
		end if
	end repeat
	
	return column_name
end get_column_name

I am really desperate, as I am too tired to solve this problem. Therefore I am asking for help on this forum :smiley: If anybody could give me a hint how to calculate column names, that would be just great!

And I need a solution made with AppleScript, because I cannot use the ‘do shell script’-command in my project. Otherwise I would just use this piece of Perl code:

my $nr = 5; # fill in position here
my $t = A;
++$t for (1…$nr-1);
print “Position: $nr → Column name: $t\n”;

Thanks so much for your help in advance, I am really looking forward to your answers.

Martin Michel

my piratunes!

Model: PowerBook 17" 1.33 GHz 1024 MB RAM
AppleScript: 1.10.7
Browser: Safari 419.3
Operating System: Mac OS X (10.4)

Got it right this time (deleted previous answer):
This only works up to 727 columns (AAY), then the xmod = 0 case has to be expanded


set n to 2
if n < 1 then set n to 1
set ab to {}
repeat with k from (n * 26 - 1) to (n * 26 + 2)
	set end of ab to get_column_name(k)
end repeat

on get_column_name(x)
	set L 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 cName to ""
	set xdiv to x div 26
	set xmod to x mod 26
	if xmod = 0 then
		if xdiv > 1 then set cName to cName & item (xdiv - 1) of L
		set cName to cName & "Z"
	else
		repeat
			set xdiv to x div 26
			set xmod to x mod 26
			if xmod > 0 then set cName to (item xmod of L) & cName
			if x > 0 then
				set x to xdiv
			else if x = 0 then
				exit repeat
			end if
		end repeat
	end if
	return cName
end get_column_name

Hi.

on get_column_name(position)
	set alphabet to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	
	set column_name to character ((position - 1) mod 26 + 1) of alphabet
	if (position > 26) then set column_name to character ((position - 1) div 26) of alphabet & column_name
	
	return column_name
end get_column_name

The name can also be got using RagTime itself, but vanilla AppleScript is probably better.

I think 127 should be “DW”. :slight_smile:

Edit: Sorry. If you’re likely to want to go into triple letters or more:

on get_column_name(position)
	set alphabet to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	set column_name to ""
	
	repeat until (position is 0)
		set column_name to character ((position - 1) mod 26 + 1) of alphabet & column_name
		set position to (position - 1) div 26
	end repeat
	
	return column_name
end get_column_name

Very clever, Nigel (should have known you’d be interested) - very crisp indeed! :lol: (and 127 is “DW”)

Without a more elaborate if statement, however, it has an upper bound of “ZZ” (27 * 26), or 702 columns – Fixed above by NG

I had a feeling Mr G. would be onto this one (I really shouldn’t spend time messing with dialogs). Once again, the similarity is… spooky: :rolleyes:

[color=gray]Edit: Original version modified substantially as follows:

    ¢ Two-way conversion, using Adam/Nigel's column-name-to-number routine (see discussion below)

    ¢ Three views:
            ¢ list (scrollable)
            ¢ number (editable)
            ¢ name (editable)

    ¢ Input field accepts either names or numbers - whether the view is by name or number

    ¢ More comprehensive input error checking

[/color]


-----------------------------------------------
(* script properties *)
-----------------------------------------------

property alpha_str : "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
property list_space : tab & tab as Unicode text
property up_arrow : list_space & tab & «data utxt2191»
property down_arrow : list_space & tab & «data utxt2193»
property max_num : 536870910
property max_name : "ASDUQSD"
property max_len : count max_name

property list_option : "List View"
property view_list : {"Number View", "Name View"}
property curr_view : view_list's beginning
property alt_view : view_list's end
property curr_btn : alt_view

property stnd_prompt : "Enter a column number or name:"
property target_index : 1 (* 1 = number, 2 = name *)
property target_item : 1 (* the current column number or name *)

-----------------------------------------------
(* calculation & support handlers *)
-----------------------------------------------

on column_name from num
	set txt to ""
	repeat while num > 0
		tell num - 1
			set txt to alpha_str's character (it mod 26 + 1) & txt
			set num to it div 26
		end tell
	end repeat
	txt
end column_name

on column_number from txt
	set num to 0
	ignoring case
		repeat with char in txt
			tell (offset of char in alpha_str)
				if it is 0 then error number 500
				set num to num * 26 + it
			end tell
		end repeat
	end ignoring
end column_number

to |set target index| to num
	set target_index to num
	set {curr_view, alt_view} to view_list's {item target_index, item (3 - target_index)}
	if curr_btn is in view_list then set curr_btn to alt_view
end |set target index|

to switch_view(num)
	if target_index is 1 then
		set target_item to column_name from target_item
	else
		set target_item to num
	end if
	|set target index| to 3 - target_index
	display_options(stnd_prompt, target_item, 1)
end switch_view

-----------------------------------------------
(* display & run handlers *)
-----------------------------------------------

to display_list(mid_pos)
	
	set list_end to down_arrow
	if mid_pos < 14 then
		set list_items to {}
		set select_pos to 1 + (mid_pos - 1) * ((mid_pos + 13) div 14)
		set mid_pos to 13
	else
		set list_items to {up_arrow}
		tell max_num - 13 to if it < mid_pos then
			set select_pos to 27 - (max_num - mid_pos) * (max_num div mid_pos)
			set mid_pos to it
			set list_end to {}
		else
			set select_pos to 14
		end if
	end if
	
	repeat with pos_indx from mid_pos - 12 to mid_pos + 13
		set end of list_items to space & pos_indx & list_space & (column_name from pos_indx) & space
	end repeat
	set list_items to list_items & list_end
	
	tell (choose from list list_items with title list_option with prompt "" default items item select_pos of list_items OK button name curr_view)
		if it is false then error number -128
		tell beginning to if it is in {up_arrow, down_arrow} then
			set target_item to word target_index of item select_pos of list_items
			my display_list(mid_pos + 26 - ((it is up_arrow) as integer) * 52)
		else
			set target_item to word target_index
			my display_options(stnd_prompt, target_item, 1)
		end if
	end tell
	
end display_list

to display_options(dlog, txt, icn)
	
	if icn is 2 then beep
	
	set {button returned:curr_btn, text returned:txt} to (display dialog dlog default answer txt ¬
		buttons {"Cancel", alt_view, list_option} default button curr_btn with title curr_view with icon icn)
	
	set txt_len to count txt
	if txt_len is 0 then return display_options("No column number or name was entered. Use 1 instead?", 1, 2)
	
	try
		
		|set target index| to 1
		set num to txt as number
		if num < 1 then
			return display_options("The number " & txt & " is too low. Use 1 instead?", 1, 2)
		else if txt_len > 9 or num > max_num then
			return display_options("The number " & txt & " is too high. Use the maximum number instead?", max_num, 2)
		else if num's class is real then
			return display_options("The number " & txt & " is not an integer. Use its rounded value instead?", num as integer, 2)
		end if
		set target_item to num
		
	on error number -1700
		
		try
			
			|set target index| to 2
			if txt_len > max_len or txt_len is max_len and txt > max_name then ¬
				return display_options("The value of \"" & txt & "\" is too high. Use the maximum name value instead?", max_name, 2)
			set num to column_number from txt
			set target_item to txt
			
		on error number 500
			
			return display_options("Only the characters A-Z (or a-z) can be used in column names. Please try again:", txt, 2)
			
		end try
		
	end try
	
	if curr_btn is list_option then return display_list(num)
	switch_view(num)
	
end display_options

display_options(stnd_prompt, target_item, 1)


So it appears that my initials are 756 in the alphabetic system, Nigel’s are 5103, Kai’s are 291 - it’s a code - I’ll be selling secret code rings in cereal boxes soon :D.


set {AB, N, d} to {"ABCDEFGHIJKLMNOPQRSTUVWXYZ", 0, 0}
repeat with aChar in reverse of characters of text returned of (display dialog "Enter your initials" default answer "")
	set {N, d} to {N + (offset of aChar in AB) * (26 ^ d), d + 1}
end repeat
N

Well, what can I say? Thank you so much for helping me out! I tried all the code pieces in my project and they work like a charm. Now I am able to finish the project and to simplify life for our staff :smiley:

I really have to admit, that this time I couldn’t get my head around the problem and I am very happy, that you solved the problem for me. Hopefully I can return the favour by giving advice on this forums myself.

Best regards from sunny Germany,

Martin

subidoo!

“l” for “l-phabet?” :wink:

Thanks, Adam! I tried to think of something like that last night myself, but I’d had rather a long day too. Now that you’ve broken the back of the problem, and I’m more compos mentis, how about this?

set {AB, n} to {"ABCDEFGHIJKLMNOPQRSTUVWXYZ", 0}
repeat with aChar in text returned of (display dialog "Enter your initials" default answer "")
	set n to n * 26 + (offset of aChar in AB)
end repeat
n

In Tiger 10.4.7, offset follows the current setting of AppleScript’s case attribute, which is, by default, ignoring case. I seem to remember that in earlier versions of Tiger, offset only heeded the case attribute when it was applied to Unicode text. With strings, it was exclusively case sensitive. Before Tiger, it was case sensitive anyway. (Ditto AppleScript’s text item delimiters, except that they’re still case sensitive with strings.) A serious user version (!) of the above script would need to take account of this. :slight_smile:

Funny you recall case sensitivity! :rolleyes:

Last night when I was doing that I had included ‘ignoring case — end ignoring’ to the calculator (thus adding two lines to the 4 I had) and ‘shortness’ trumped ‘safety’ in my sleepy (read: a few ales) state. I wasn’t happy with reversing the characters, either, but didn’t think of your direct approach of simply multiplying by 26 every time through. Thanks. :slight_smile:

Couldn’t resist one more rather unsophisticated ‘kick at the cat’. No error checking for number overflow, so short words, and punctuation is ignored. With the numbers added, it is base 36.


set Str to words of "Bring the loot to the pub at 7 PM sharp"

set SecretMsg to getCodes(Str)
--> "4589866 37987 822485 1066 37987 30926 65 21 847 47544719"

to getCodes(Str)
	set AB to "AB1CD2EF3GH4IJ5KL6MN7OP8QR9ST0UVWXYZ"
	repeat with aWord in Str
		set n to 0
		repeat with aChar in aWord
			set n to n * 36 + (offset of aChar in AB)
		end repeat
		set contents of aWord to n
	end repeat
	set AppleScript's text item delimiters to space
	set code to Str as text
	set AppleScript's text item delimiters to ""
	return code
end getCodes

set ClearText to decode(SecretMsg)
--> "BRING THE LOOT TO THE PUB AT 7 PM SHARP"

to decode(code)
	set AB to "AB1CD2EF3GH4IJ5KL6MN7OP8QR9ST0UVWXYZ"
	set CD to {}
	set wds to words of (code as text)
	repeat with n in wds
		set cName to ""
		repeat until (n is 0)
			set cName to character ((n - 1) mod 36 + 1) of AB & cName
			set n to (n - 1) div 36
		end repeat
		set end of CD to cName
	end repeat
	set AppleScript's text item delimiters to space
	set Ans to CD as text
	set AppleScript's text item delimiters to ""
	return Ans
end decode

:lol: I was regarding it as list (of sorts) ” even though it’s not. Like you, I opted to use a string in place of the original list since (unless one goes for list referencing) it can be a bit faster. (In addition, as we’ve since seen, the string form is also quite handy for using offset to reverse the conversion process.)

Now that’s really cunning, Nigel. :cool:

Before being called away on other matters, I’d also started to tweak Adam’s reverse conversion ” with a view to incorporating it into my script. However, since the method I had in mind still required some positional information, I can’t claim a similar approach this time. :stuck_out_tongue:

Anyway, on my return, I thought I’d finish what I started ” so I’ve replaced my earlier script with a modified version that handles conversion in either direction ” incorporating your nifty refinement of Adam’s already nifty routine. (And the good news is that, since I pulled some stuff into the script’s top level, I’ve also extended many of the variable labels.)

See message #5 above for the revised version…

Slick. You do like really neat dialogs :lol:

I looked at my Office X version of Excel, and it only supports 256 columns: IV is the largest column heading.

Thanks, Adam.

Yes, I’m aware of Excel’s current limits (and I shouldn’t think there are too many alternative spreadsheets that exceed them).

However, for this exercise, I opted for the highest integer value available in AppleScript. This is normally 536870911 (2 ^ 29 - 1), and any attempt to use an integer value beyond that may result in a coercion failure [error number -1700]. Even then, the use of a repeat loop at that magnitude can cause a numeric overflow [error number -2702] ” which is why the limit was set to 536870910.

set result_list to {}
set hi_value to 536870908
repeat with added_value from 1 to 5
	set curr_value to hi_value + added_value
	set curr_result to "Repeat loop successful."
	try
		repeat with some_variable from curr_value to curr_value
		end repeat
	on error err number num
		set curr_result to "Error number " & num & return & tab & tab & err
	end try
	set result_list's end to "value:" & tab & curr_value & return & "class:" & tab & curr_value's class & return & "result:" & tab & curr_result
end repeat
set text item delimiters to return & return
set result_list to return & result_list
set text item delimiters to {""}
display alert "Practical maximum integer value: 536870910" message result_list


RagTime’s spreadsheets (as used by Martin) are 16000 * 16000, the bottom right corner being cell WQJ16000 ” still well within the range of the scripts above. (It’s also possible to add up to 16000 planes to make a three-dimensional spreadsheet. But let that pass.)

It’s possible to use a different sort of repeat so that the index doesn’t have to be an integer, but you may as well impose a limit somewhere! :slight_smile:

At $1200 Canadian for Ragtime, I’ll stick to the Office X version of Excel.

I myself have RagTime Solo, which is free as long as it’s only used for domestic purposes and on one computer. I must admit I haven’t got far beyond using its spreadsheets (with some cunningly devised scripts) to keep track of my home accounts. It’s also occasionally useful for reading Word documents, which some people think are a good idea to send to everyone. :confused:

Ahh, thanks. I’ve no doubt that Office X will cease to function at some point, and I don’t do enough word document stuff to warrant buying a later version. I do have a lot of spreadsheets, however, and if Ragtime will open Word, it may well open Excel as well.

It seems to be able to. According to the reference pdf that comes with RagTime Solo 5.6.4, the converter’s only been tested with Excel’s Mac versions 4, 5, 98, and 2001, but may be compatible with later versions too.

Right. At one point, I changed the repeat loop for one that contained the counter incrementation ” so that, in cases where real numbers had to be used, they could be displayed in scientific notation. However, since it didn’t look very pretty (and because I didn’t fancy adding a further handler just to clean up the appearance), I decided that integer values should cover most requirements.

Thanks, BTW, for the info about RagTime/Solo, which I know of ” but haven’t tried. I may just take a look at that… :slight_smile:

I have one important Office X Spreadsheet with about 25 sheets. Unfortunately, Ragtime only converts the first one.