Separate multiple comma separated values in Numbers

One of the CSV files I'm using has lumped the address into one single cell, separated by commas.

For example: 12 Fairy Tale Lane, Wonderland, Oz, Neverland

I need to separate these into Address 1, Address 2, Address 3 and City columns

Solution 1:

Here is a suggestion from https://discussions.apple.com/message/26532955#26532955

In order to import comma-separated CSV into Numbers.app properly, you need to set the decimal separator to period in System Preferences. If decimal separator is set to comma, Numbers.app can only import semicolon-separated CSV.

1. Quit Numbers.app;
2. Set the decimal separator to period, temporarily, in System Preferences; [/Language & Region/Advanced]
3. Launch Numbers.app, import the CSV file and save the resultant Numbers file;
4. Quit Numbers.app;
5. Reset the decimal separator to the original (presumably, comma) in System Preferences;

Solution 2:

CSV files are just plain text files; you could use something like BBEdit to edit, search, replace and then save the file.

In CSV you can also encapsulate values in between " So your CSV file should in fact look like: "Address 1","Address 2","Address 3" please note: no space after the coma and before the opening double-quote.

Bonus: while editing your CSV, you can also use the vertical editing mode in OS X: press option key and click-drag-vertically to select a column of text. Once the column selected you can start typing: it will write what you type on all the lines. It's like magic!

However, by experience, I would advise you to get to the source of the CSV file and make it use the double quote.

Hope this helps! Good luck!