I written a script to move files depending on certain criteria in a Excel spreadsheet,
which works fine.
I’m in the process of making the script a little more user friendly, part of that involves
asking the user for certain cell ranges rather than hard-coding them into the script.
Here come’s the question, I get a cell range back from the user, say ‘K4:K10’, that’s
how they’re prompted to enter the range, what would be the easiest way to get the
numerical ‘row’ values from the result returned? So I’m after the 4 and 10. I can split
the two parts using ‘:’ as the applescript text delimiter, it’s getting the numbers I’m
after.
Please can i people’s ideas before I try and code something.
You can always use something like this to filter the number out of a text, I guess:
set tempSwitch to false
set theNumbers to ""
repeat with i from 1 to (count characters of 'your string here')
set tempChar to character i of 'your string here'
if tempChar is in {"0","1","2","3","4","5","6","7","8","9"} then
set tempSwitch to true
else
set tempSwitch to false
end if
if tempSwitch is true then
set theNumbers to theNumbers & tempChar
end if
end repeat
set theNumbersConverted to theNumbers as integer
But there must be a shorter/better way to do this, no?
Thanks for the reply, will have a go at incorporating it.
I may have a quick trawl through the applescript guide to see if there’s some sort
of function. Having worked with Coldfusion I know there’s an ‘isNumeric’ function
that can be used.
While there may be a scripting addition that does what you want, Nick, a vanilla repeat loop is still a reasonable approach for this kind of thing.
Here’s a slight variation on caenel’s suggestion:
on row_nums from l
tell "0123456789" to repeat with i in l
repeat with n from 1 to count i
if i's character n is in it then
set i's contents to i's text n thru -1 as integer
exit repeat
end if
end repeat
end repeat
l
end row_nums
row_nums from words of text returned of (display dialog "Enter an Excel range (eg: K4:K10)" default answer "K4:K10")
--> {4, 10} (* assuming default entry *)
That’s nice and neat.
I written one that works it’s way through checking the ascii value of each letter to see if
it’s within the range of values for a number.
I think I’ll be implementing your solution though, much tidier.
Just for the fun, I added some code to get the columns numbers too
on row_nums(l)
tell "0123456789" to repeat with i in l
repeat with N from 1 to count i
if i's character N is in it then
set i's contents to i's text N thru -1 as integer
exit repeat
end if
end repeat
end repeat
return l
end row_nums
on col_letters(l)
tell "0123456789" to repeat with i in l
repeat with N from 1 to count i
if i's character N is in it then
set i's contents to i's text 1 thru (N - 1)
exit repeat
end if
end repeat
end repeat
return l
end col_letters
on col_nums(l)
set nl to {}
repeat with k in l
set k to k as text
if length of k > 1 then
set {n1, n2} to {my base26(character 1 of k), my base26(character 2 of k)}
set N to n1 * 26 + n2
else
set N to my base26(k)
end if
copy N to end of nl
end repeat
return nl
end col_nums
on base26(t)
set x to (ASCII number t) - 64
if x > 26 then set x to x - 32
return x
end base26
(*
row_nums from words of text returned of (display dialog "Enter an Excel range (eg: K4:K10)" default answer "K4:K10")
*)
set range to "K4:IV10"
set {r1, r2} to my row_nums(words of range)
set {c1, c2} to my col_nums(my col_letters(words of range))
log {r1, r2, c1, c2}
Yvan KOENIG (from FRANCE vendredi 10 novembre 2006 16:31:42)
Yeah, that’s another way to do it, Nick. If there are a number of characters to check, converting from ASCII character to ASCII number (or vice versa) can carry a bit of a performance overhead. (Not too critical in this situation, but something to bear in mind if you’re processing gobs of text.)
I wondered when somebody would get around to posting a way of getting column numbers, too, Yvan. I also like Jacques’ idea of using Excel for the translation.
In fact, when I hurriedly read Nick’s initial post earlier today, I started out with just such a routine ” thinking that we might be looking at a mirror of the fun we had with the discussion on how to convert decimals to column names (spreadsheet app).
FWIW, what I ended up with was something like this:
property alpha : "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
on col_row from r
set n to 0
set c to count r
repeat with i from 1 to c
tell (offset of r's character i in alpha)
if it is 0 then return n & (r's text i thru c as integer)
set n to n * 26 + it
end tell
end repeat
end col_row
set r to text returned of (display dialog "Enter an Excel range (eg: K4:AB10)" default answer "K4:AB10")
set {col_start, row_start, col_end, row_end} to (col_row from r's word 1) & (col_row from r's word 2)
(* demo only *)
{col_start:col_start, col_end:col_end, row_start:row_start, row_end:row_end}
--> {col_start:11, col_end:28, row_start:4, row_end:10}
Of course, I didn’t post at the time ” believing that it might be a bit OT.
