split comma separated text into columns

Question

Level 1

8 points

split comma separated text into columns

I have imported a transaction list from an online financial management site into Numbers. All my transactions are tagged, usually 2 times: a category and a subcategory. these tags are in one column in my Numbers table separated by a comma. is there an easy way to separate these into independent columns so that I can easily create subcategories in Numbers?

I looked at Split but that seems to be the same as making a copy of the column (in that case why is a split command needed, please enlighten me).

thanks.

MacBook Pro, Mac OS X (10.5.8)

Posted on Dec 21, 2009 12:22 PM

Reply

Answer 1

Dec 21, 2009 1:09 PM in response to mbucc

m,

To decompose your tag column, we would create 2 new columns with formulas that each take a portion of the tag, before the comma and after the comma.

We can use FIND or SEARCH to locate the comma and then we can use combinations of LEFT, RIGHT, MID and LEN to grab the appropriate sub strings. The exact form of the expressions would depend on whether you have spaces or other characters that you want to strip out.

Regards,

Jerry

Reply

Answer 2

Badunit

Level 7

30,660 points

Dec 21, 2009 8:15 PM in response to mbucc

Here are two threads on the subject:

http://discussions.apple.com/thread.jspa?messageID=10364600&#10364600
http://discussions.apple.com/thread.jspa?messageID=10158963

If you do this often I would recommend using the script that Yvan supplied. If it is a one-time-only affair, you can do it quickly with any text editor.

Reply

Answer 3

mbucc Author

Level 1

8 points

Dec 22, 2009 3:58 PM in response to mbucc

thanks a lot, script works like a charm. Unfortunate there's no "Text to Columns" in Numbers....

Reply

Answer 4

Badunit

Level 7

30,660 points

Dec 22, 2009 5:46 PM in response to mbucc

mbucc wrote:
...Unfortunate there's no "Text to Columns" in Numbers....

If you saved the script in the scripts folder and called it Text to Columns, there is now.

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Dec 22, 2009 10:51 PM in response to mbucc

My script is named "csv2tsv", not "Text to Columns".
As it is not linked to Numbers, the given instructions aren't urging you to install it in the folder
Macintosh HD:Users:yvan_koenig:Library:Scripts:Applications:Numbers:

but in the folder

Macintosh HD:Users:yvan_koenig:Library:Scripts:

If you applied the instructions, the script is available from every applications.
If you want to restrict it to Numbers, install it in :
Macintosh HD:Users:yvan_koenig:Library:Scripts:Applications:Numbers:

As badunit wrote, you may rename it but if you want to ask about it, use the original name in your question. If you ask about a script named "Text to Columns" I would be unable to guess which one it is.

Yvan KOENIG (VALLAURIS, France) mercredi 23 décembre 2009 07:51:43

Reply