Hi. I’m not experienced in writing script, but I have a couple of tasks I have to do regularly after receiving a .csv file that I save as xlsx.
I have this script:
tell application “Microsoft Excel”
set AppleScript’s text item delimiters to “-”
text to columns (range “l2:L200” of worksheet “Sheet1” of active workbook) destination range “m2” data type delimited
I am trying to separate out the last 4 digits of a zipcode that is formatted as 00000-0000 so that everything after the dash goes into the next column. I do this well with the text-to-columns option under the Data heading, but when I run the script, It doesnot separate out the last four into another column. It keeps everything together and puts it all into the designated column.
I appreciate any help.
Excel doesn’t know (or probably care) about AppleScript’s text item delimiters, but the following should work:
tell application "Microsoft Excel"
tell sheet 1 of active workbook
text to columns range "A1:A200" destination range range "B1:B200" data type delimited other char "-" with use other
The source range in this example is column A, the destination is column B. If you already have data in the destination column, Excel will display a warning.
I don’t know if this would work with a multiple-column source which you may be specifying in your script (there’s no difference on screen between an upper-case “I” for “Interesting” and a lower-case “l” for “large”).
Tested with Excel v16.16.27
FWIW, if you dump it into textedit (plain text mode, depending upon your font) you should see that it was a lower-case ‘L’. Or… put it inside a code block
range “l2:L200” or range “i2:I200”
Thank you so much for your help. Every time I get one of these files I have to spend about an hour on formatting to make it useful, changing date formats, zipcode formats, hiding unused columns, fitting width to text, etc. Getting a script to run thes changes will be an investment now, but save hours of tedium later. With this, I have it about halfway done.
Thanks also for the tip on text edit.