macosnumbers
I've got a column where each cell in the column contains two different pieces of data separated by a known delimiter ", ". How can I split each cell in this column around the delimiter?
Given:
COLUMN1
-------
ABC, 123
FOO, 666
ROFL, 411
I want
COLUMN1 COLUMN2
------- -------
ABC 123
FOO 666
ROFL 411
Thanks to your sample file, I think I was able to do what you wished.
Using an additional column to bring in the numbers corresponding to the codes (16AA, 1; 16AE, 2...) sorting by that column then hiding it the desired result was accomplished. To refresh the sort after adding data, unhide the column and sort again.
Here are the steps:
Create new column to the left of the "Code" column.
Add a formula to extract the proper number for each code and copy down.
Formula: =INDEX (Group::c, MATCH (B, Group::A))
(Using INDEX(MATCH) is a good way to get column data with out worrying about extra columns being added as is the case with VLOOKUP.)
Result:
The great thing about numbers is they offer a great how-to guide. If you don't know how to do something then all you have to is start typing a formula and it will begin to auto populate, click on it and the functions sidebar will open with the function. it will give you examples, what the parameters are etc.
That's just a tip, but your lucky I'm here:
Since you didn't specify what you want the cell of Table 1 to count in the column of table 2 I just counted all non-blank cells. You can alter with whatever text based of the countif function in the second example that compares against a string:
1 : Table 1 Cell that stores number of cells in column A of Table 2. As described since you didn't specific I just counted all non-blank cells :
=COUNTIF(Table 2::A,"<>"&"")
2 : Table 1 Cell that stores number of cells in Column E of Table 2 that matches text "MATCHED" :
=COUNTIF(Table 2::E,"MATCHED")
Instead of typing you can just do this:
Type into the cell = then type COUNTIF then click on the column header (E) it will select the entire column, then click on condition and type "MATCHED" then press enter! Your outcome will be based off this :
Best Answer
You can also replace the delimiters with tabs: