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:
- Copy-paste the script into Script Editor (in Applications > Utilities)
- Select the cells in the column with the addresses you want to split
- With the cells selected click the "run" button in Script Editor, and wait for the notification to paste.
- Click once in the top-left cell of the range where you want to paste the values.
- 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