Excel text to colums delimited

Scripting Forums AppleScript | Mac OS X
#1

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”
activate
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
end tell

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.
Thanks.

#2

Hi,

Excel doesn’t know (or probably care) about AppleScript’s text item delimiters, but the following should work:

tell application "Microsoft Excel"
	activate
	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
	end tell
end tell

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

#3

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 :slight_smile:

range “l2:L200” or range “i2:I200”
#4

It works!!!
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.

2 Likes