I’m working with a list of names in excel, for example:
Micheal Jordan
Derek Lamb
Tatyana M Waffle
Jeremy Hostess
George Bush Jr.
The names are in column A - I’m working on splitting them into separate columns.
Excel’s text to column feature would work - but I can’t figure out how to access it with apple script?
So I’m working with this:
tell application "Microsoft Excel" to activate
tell application "Microsoft Excel"
tell worksheet "Sheet1" of active workbook
set thedata to value of range "A2"
end tell
set AppleScript's text item delimiters to {" "}
set value of range "A2" to word 1 of thedata
set value of range "B2" to word 2 of thedata
set value of range "C2" to word 3 of thedata
end tell
Issues:
If there is only a first and last name, applescript errors because there is no word three to set C2 to.
I need to make this script repeat for each row… and copying and pasting the script 150 time seems to kinda defeat the purpose of using applescript! Not to mention, there must be a more elegant way to do it.
You could change your variable thedata so that there are always three words
tell application "Microsoft Excel"
tell worksheet "Sheet1" of active workbook
set thedata to value of range "A2" & " " -- 3 spaces are added to the value
end tell
set AppleScript's text item delimiters to {" "}
set value of range "b2" to text item 1 of thedata
set value of range "c2" to text item 2 of thedata
set value of range "d2" to text item 3 of thedata
end tell
The script should add three spaces to the value of A2, but the message board collapses these into one.
or
you could use Text To Columns
tell application "Microsoft Excel"
text to columns (range "A1:A10" of worksheet "Sheet1" of active workbook) destination range "B1" data type delimited with space
end tell