Can anyone point me in the right direction? I’ve hardly scripted Excel before.
I have used the ‘Proper’ function on a column which is all caps, but this results in some words such as ‘Tv’, ‘Pvc’ etc which I actually want in caps.
I’d like to write a script (or can I do it within Excel??) to trawl through this column and replace ‘Tv’ with ‘TV’ etc. There are probably 20 or 30 different possibilities. I tried using Excel’s ‘Substitute’ function, but that only does one at a time and I really don’t want to end up with that many extra columns!
Thanks in advance, if anyone’s still around this close to Christmas…
Unless you’ve used Visual Basic before, it’s probably easier to grab the offending column, get it from the clipboard into an AppleScript, work on it there, and paste it back. You can even export the spreadsheet as comma delimited text and read that into an AppleScript. Before you go there, however, hope that some of the gurus of this forum jump in with better advice.
OK, I guess my solution is to save as tab separated, put it in Quark and use my tried and trusted Quark Search and Replace script. It adds a lot of extra unnecessary steps, though…
Thanks for letting me know I’m not being totally stupid!
I’m using Office X, but you can try the following script. It changes the value of the cells in column 1 to all caps.
tell application “Microsoft Excel”
–Activate
set d to UsedRange of Worksheet 1
set c to Column 1 of d
set cell_count to count Cells of c
repeat with i from 1 to cell_count
set t to Value of Cell i of c
set char_count to length of t
set temp_chars to {}
repeat with j from 1 to char_count
set this_char to character j of t
set av to ASCII number this_char
if av > 96 and av < 123 then
set this_char to (ASCII character (av - 32))
end if
set end of temp_chars to this_char
end repeat
set Value of Cell i of c to (temp_chars as string)
end repeat
end tell
If you’re not using Office X, then you might have a problem with UsedRange because they changed wordings. I THINK the rest of the script is generic to newer versions.