separate addresses into different columns in numbers

Front Desk has exported the addresses into a single column but I need the addresses separated into 4 separate columns: Street Address, City, State, Zip Code.

I think, if this is talking about the same software, that Front Desk exports to a .csv (character-separated-values file).

In Numbers have you tried File > Open and then navigate to the file and open it? That often works.

If after you try that, the result still ends up in one column in Numbers then you could try this CSV to Tabs on Clipboard Automator Service. (Dropbox download).

To install, double-click the download .workflow package and, if necessary, click 'Open Anyway' in System Preferences > Security & Privacy.

Thereafter, to use:

1. In Finder (not Numbers) right-click the .csv file exported from Front Desk.
2. Choose CSV to Tabs on Clipboard under Services in the contextual menu.
3. Respond to the prompts from the script.
4. When the notification appears that the script is done, click once in a Numbers cell and command-v to paste. The data should appear in separate columns.

SG

I see the problem. That's not so easy because the addresses are not uniform. Some have a comma between address and city, and some have a linefeed.

If you are using Numbers 3 then the script below should help. To use it:

1. Copy-paste the script into Script Editor (in Applications > Utilities)
2. Select the cells in the column with the addresses you want to split
3. With the cells selected click the "run" button in Script Editor, and wait for the notification to paste.
4. Click once in the top-left cell of the range where you want to paste the values.
5. Type command-v to paste.

If all goes well, you should get something like this:

If you have zip codes with leading zeros, first format that column as text before pasting the results from the script.

It's better to not have blank rows in the middle of the data, but the script may be able to handle that gracefully.

If you have problems, post a screenshot of results and some adjustments to the script should do the trick. This works in Numbers 3. If you're still using Numbers 2, then the script will need modification.

SG

tell application "Numbers"

tell document 1's active sheet

tell (first table whose selection range's class is range)

tell selection range

set pasteStr to ""

repeat with c in cells

set v to c's value

set pasteStr to pasteStr & my parseAddress(v)

end repeat

end tell

end tell

end tell

end tell

set the clipboard topasteStr

display notification "Click a cell once and command-v to paste"

to parseAddress(s)

try

set zip to s's word -1 -- last "word"

set state to s'sword -2 -- second to last word

set AppleScript'stext item delimiters to {",", linefeed}

set sParts to s's text items

if sParts's length = 3 then

set street to sParts'sitem 1

set city to sParts's item 2

else

set street to sParts's item 1 & " " & sParts's item 2

set city to sParts's item 3

end if

set AppleScript'stext item delimiters to ""

return street & tab & city & tab & state & tab & zip & return

on error

return return-- a "blank" for that line

end try

end parseAddress